February 3, 2005 at 7:45 am
Hello,
I need to write SQL than gets results for each Desc in one row.
ID Desc
1 ABC
2 ABC
3 ABC
4 CBD
5 CBD
6 DEF
So, result should look like this:
Desc ID1 ID2 ID3
---------------------------------------
ABC 1 2 3
CBD 4 5 NULL
DEF 6 NULL NULL
Thank you everyone for any effort
February 3, 2005 at 9:00 am
I think this may require a cursor and Dynamic SQL - neither of which I am very familiar with.
Here is a solution similar to another currently active thread:
select desc,
SUM(CASE WHEN ID = 1 THEN ID ELSE NULL END) AS ID1,
SUM(CASE WHEN ID = 2 THEN ID ELSE NULL END) AS ID2,
SUM(CASE WHEN ID = 3 THEN ID ELSE NULL END) AS ID3,
SUM(CASE WHEN ID = 4 THEN ID ELSE NULL END) AS ID4,
SUM(CASE WHEN ID = 5 THEN ID ELSE NULL END) AS ID5,
SUM(CASE WHEN ID = 6 THEN ID ELSE NULL END) AS ID6
from YourTable
GROUP BY desc
That yields:
descr ID1 ID2 ID3 ID4 ID5 ID6
----- ----------- ----------- ----------- ----------- ----------- -----------
ABC 1 2 3 NULL NULL NULL
CBD NULL NULL NULL 4 5 NULL
DEF NULL NULL NULL NULL NULL 6
Maybe a COALESCE with a CASE could put this in line somehow.
Ideas?
Ryan
February 3, 2005 at 12:07 pm
This does not work because this numbers are just as an example. In fact, there are 3000 rows in a table, and each Desc can have up to 5 different IDs. This table has 1 to many relationship with another and Desc is id in second table. Anyway, thank you.
February 3, 2005 at 12:19 pm
If you provide table DDL and design constraints up front, you can avoid forum participants wasting theirs and your time coming up with solutions based on incorrect/missing details.
Your initial post indicated an ascending ID sequence in 1 table, but in reality, there are 2 tables and a max ID of 5 ?
rhunt's solution is correct, you just need to remove ID6 and add the table join.
February 3, 2005 at 3:12 pm
Thank you everyone.
I found good solution:
SELECT a.desc, min(a.id) id1, min(b.id) id2, min(c.id) id3
FROM your_table a
LEFT JOIN your_table b ON a.id < b.id and a.desc = b.desc
LEFT JOIN your_table c ON b.id < c.id and a.desc = c.desc
GROUP BY a.desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply