SQL将多个行转置到不同的列

Question

我有SQL server数据表，像这样:

<表类> ID SYMPTOM1 SYMPTOM2 tbody><<tr>1B1CD2EF3C3DE

你可以这样做，作为一个自连接-使用行号有另一列，要么是1或2(取决于多少次ID出现)，1应该总是出现，2有时出现，所以左连接那些行2到那些行1(基于ID)给你的最终结果。

WITH x AS(
SELECT
t.ID,
t.SYMPTOM1,
t.SYMPTOM2,
ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.SYMPTOM1) as rn
FROM t
)
SELECT
*
FROM
x x1
LEFT JOIN x x2 ON x1.id = x2.id AND x1.rn = 1 AND x2.rn = 2

可以选择行号

SELECT
t.ID,
SYMPTOM1 = MAX(CASE WHEN t.rn = 1 THEN t.SYMPTOM1 END),
SYMPTOM2 = MAX(CASE WHEN t.rn = 1 THEN t.SYMPTOM2 END),
SYMPTOM3 = MAX(CASE WHEN t.rn = 2 THEN t.SYMPTOM1 END),
SYMPTOM4 = MAX(CASE WHEN t.rn = 2 THEN t.SYMPTOM2 END)
FROM (
SELECT
t.*,
rn = ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY (SELECT 1))
FROM YourTable t
) t
GROUP BY t.ID;