The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.
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
22.3k9129262
accept rate: 40%

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:

×34

question asked: 04 Sep '14, 09:24

question was seen: 899 times

last updated: 04 Sep '14, 10:41