sql - 在SQL Server中将多行相同id的行合并为一行

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    |
+----+------+------+------+

我想要这样的桌子：-

+----+-----+------+------+------+-----+------+------+------+----+
| 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   |
+----+-----+------+------+-----+------+------+------+------+----+

标签： sqlsql-server

Answer 1

您可以使用条件聚合：

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;