cancel
Showing results for 
Search instead for 
Did you mean: 

How do I transform the following result in IQ?

0 Kudos
2,905
tradedate,sysmbol,clientid
20140902,MSFT,DCA
20140902,MSFT,BVX


change to

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

Accepted Solutions (0)

Answers (1)

Answers (1)

MarkCulp
Participant

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

0 Kudos

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
Participant
0 Kudos

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.

0 Kudos

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.