results of query in one row ?!

[bartvb]

I have a table who looks like this
****A**** ****B**** ****C****

20005454 abc 01
20005454 aqr 02
20005445 q 01
20001212 b1 01
20001212 baq 02
20001212 rst 03

i want a query that gives the following result (but each time in one row !!!!!!)

for 20005454=> abc , aqr
for 20005445=> q
for 20001212=> b1, baq, rst

(column C indicates the occurence of value A in that table)

Thanx very much, Bart

 

[carp]

Sorry, Bart, but I don't believe you can do this in SQL. You will need some procedural method that collects everything from column B for a given value and strings it all together.

 

[bartvb]

=> solution:

http://www.orafans.com/ubb/Forum6/HTML/003287.html

 

[carp]

Yes, this is a specific solution, but requires you to know exactly what the maximum number of items for any given value is going to be (three in this case). If you base an application on this query and later it turns out that one value has four items instead of three, you will get erroneous results. Also, this is going to be a very cumbersome approach if you run into a situation where you have many items (e.g., 50 or 100) for a given value. In this case, you are going to be doing an outer join on 50 or 100 tables - not something that sounds pleasant!

On the other hand, you could probably build a procedure that determines the maximumn number of items per value and dynamically builds and executes the query. But again, this will take a procedural approach rather than pure SQL.

 

[bartvb]

Yep, your remarks are correct !!

(I must say I didn't think of that at first...)

But in this case, if there are more than three items, (which only occurs 1/12000) there's no need to mention the fourth one.

But, thanks for your remarks!!