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

change to

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

asked 04 Sep '14, 09:24

IQGeek's gravatar image

IQGeek
0336
accept rate: 50%


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%20Culp's gravatar image

Mark Culp
24.8k10139296
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×41

question asked: 04 Sep '14, 09:24

question was seen: 2,659 times

last updated: 04 Sep '14, 10:41