Merge multiple rows of same id into one row in SQL Server

Question

+----+------+------+------+
| ID |AddID |   A  |  B   |
+----+------+------+------+
|  1 | 1    |  1   | 31   |
|  1 | 2    | NULL | 1    |
|  2 | 1    | 38   | 4    |
|  2 | 2    | NULL | NULL |
|  3 | 1    | NULL | NULL |
|  3 | 2    | NULL | NULL |
|  4 | 1    | 1    | NULL |
|  4 | 2    | NULL | 5    |
|  4 | 3    | NULL | 5    |
+----+------+------+------+

I want my table like this:-

+----+-----+------+------+------+-----+------+------+------+----+
| ID |Add1 |Add2  |Add3  |  A1  | B1  |  A2  |  B2  |  A3  | B3 |
+----+-----+------+------+------+-----+------+------+------+----+
|  1 | 1   | 2    |NULL  |1     | 31  | NULL | 1    |NULL  |NULL|
|  2 | 1   | 2    |NULL  |38    | 4   | NULL | NULL |NULL  |NULL|
|  3 | 1   | 2    |NULL  |NULL  | NULL| NULL | NULL |NULL  |NULL|
|  4 | 1   | 2    | 3    |1     | NULL| NULL | 5    | NULL |5   |
+----+-----+------+------+-----+------+------+------+------+----+

Some kind of PIVOT? Or a GROUP BY with case expressions to do conditional aggregation. — jarlh, Dec 20 '19 at 14:58
Great! Thanks for telling us and good luck. If you get stuck, don't forget to post a question, show us your attempts, and explain why they aren't working. — Thom A, Dec 20 '19 at 15:05
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Piotr Palka, Dec 20 '19 at 15:40

score 3 · Answer 1 · answered Dec 20 '19 at 15:00

You can use conditional aggregation:

select id,
       max(case when addid = 1 then addid end) as add1,
       max(case when addid = 2 then addid end) as add2,
       max(case when addid = 3 then addid end) as add3,
       max(case when addid = 1 then a end) as a_1,
       max(case when addid = 1 then b end) as b_1,
       max(case when addid = 2 then a end) as a_2,
       max(case when addid = 2 then b end) as b_2,
       max(case when addid = 3 then a end) as a_3,
       max(case when addid = 3 then b end) as b_3
from t
group by id;

Merge multiple rows of same id into one row in SQL Server

1 Answers1