sql - 如何在 SQL Server 中将 2 列拆分为 4 列？

Question

我有一个查询，它显示了一个包含 Id、问题和回复的表。我需要将我的列问题和回复拆分为 4 列（Q1、R1、Q2、R2）。如何同时使用两列执行此操作？我找到了答案，只有一列到两列才有帮助。

标签： sqlsql-server

Answer 1

您可以使用条件聚合：

select id,
       'Q1' as question_1,
       max(case when question = 'Q1' then reply end) as reply1,
       'Q2' as question_2,
       max(case when question = 'Q2' then reply end) as reply2
from t
group by id;

编辑：

您可以使用相同的想法row_number()：

select id,
       max(case when seqnum = 1 then question end) as question1,
       max(case when seqnum = 1 then reply end) as reply1,
       max(case when seqnum = 2 then question end) as question2,
       max(case when seqnum = 2 then reply end) as reply2
from (select t.*,
             row_number() over (partition by id order by question) as seqnum
group by id;