How do I transform the following result in IQ?

[former_SQLA_member1694944]

tradedate,sysmbol,clientid
20140902,MSFT,DCA
20140902,MSFT,BVX


change to 
tradedate,symbol,clientID
20140902,MSFT,DCA|BVX

[MarkCulp]

You can easily do this using a GROUP BY query and the LIST() operator.

Example.

SELECT tradedate, symbol, list( clientid, '|' )
  FROM T
 GROUP BY tradedate, symbol;


HTH

[former_SQLA_member1694944]

Thanks This works.

I have another question T (table) in my case is really a query (subquery in this case). Even though the subquery only returns 50 records and it is fast when I run it alone, but very slow when I use it as subquery using the above example. is there a way I can use a hint to ask IQ to get the result for subquery first and then do the Group by?

[justin_willey]

Do you have foreign key(s) that the database can use to make the join - if so it should use them without hints. It might be worth posting the table, index and foreign key definitions - that way you are more likely to get an informed answer. Probably best as a new question.

[former_SQLA_member1694944]

I can select into #temp table and then select, it is fast to do this way. but just wondering if I can do without temp table solution. In SQL server, we can get away with CTE (Common table Expression). I'm not sure how to do in IQ.