SAP SQL Anywhere

Question

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

change to

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

Answer 1

2

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

permanent link

answered 04 Sep '14, 09:32

Mark Culp
24.8k●10●139●296
accept rate: 41%

edited 04 Sep '14, 09:32

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?

(04 Sep '14, 09:56) IQGeek

Replies hidden

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.

(04 Sep '14, 10:08) Justin Willey

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.

(04 Sep '14, 10:14) IQGeek

flat view

SAP SQL Anywhere

How do I transform the following result in IQ?

Follow this question

Related questions