SQL Server：具有自定义列名称的数据透视

Question

我需要使用自定义列名来旋转表。请参阅下面的表格格式。

当前格式 ：

ID            question                            Answer
4482515   I would like to be informed  by mail.   No
4482515   Plan to Purchase?                       Over 12 months
4482515   Test Question Text                      some Answer

我想以以下格式显示数据。

所需格式 ：

ID       question 1                            Answer1   question 2         Answer 2
4482515  I would like to be informed  by mail. NO        Plan to Purchase?  Over 12 months

请注意：我不知道连续出现的问题和答案的数量，因此应动态生成列的question1 Answer1。

谢谢

编辑：谢谢您的帮助，我尝试一下您提供给我的动态代码，并得到此错误。

Msg 8167, Level 16, State 1, Line 1
The type of column "answer" conflicts with the type of other columns specified in the UNPIVOT list.

我的桌子是

RID         Question    Answer
4482515 Some Question1  Some Answer1
4482515 Some Question2  Some Answer2
4482515 Some Question3  Some Answer3
4484094 Some Question1  Answer1
4484094 Some Question2  Answer2
4484094 Some Question3  Answer3
4484094 Some Question4  Answer4

我打印出SQL，结果如下。

SELECT rid, [question1],[answer1],[question2],[answer2],[question3],[answer3],[question4],[answer4],[question5],[answer5],[question6],[answer6]
              from
              (
                select rid,
                  col+cast(rn as varchar(10)) col,
                  value
                from
                (
                  select rid, question, answer,
                    row_number() over(partition by rid order by rid, question) rn
                  from #tmp_question
                ) src
                unpivot
                (
                  value
                  for col in (question, answer)
                ) unpiv
              ) d
              pivot 
              (
                  max(value)
                  for col in ([question1],[answer1],[question2],[answer2],[question3],[answer3],[question4],[answer4],[question5],[answer5],[question6],[answer6])
              ) p

我的原始SQL代码也在下面

    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10))) 
                    from 
                    (
                      select row_number() over(partition by rid 
                                               order by rid, question) rn
                      from #tmp_question
                    ) d
                    cross apply
                    (
                      select 'question' col, 1 sort union all select 'answer', 2
                    ) c
                    group by col, rn, sort
                    order by rn, sort
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT rid, ' + @cols + '
              from
              (
                select rid,
                  col+cast(rn as varchar(10)) col,
                  value
                from
                (
                  select rid, question, answer,
                    row_number() over(partition by rid order by rid, question) rn
                  from #tmp_question
                ) src
                unpivot
                (
                  value
                  for col in (question, answer)
                ) unpiv
              ) d
              pivot 
              (
                  max(value)
                  for col in (' + @cols + ')
              ) p '

--print @query
execute(@query);

等待您的帮助！

Answer 1

您可以通过几种方法来执行此操作。

如果您有已知数量的问题/答案，则可以将其row_number()与聚合函数和CASE表达式一起使用：

select id,
  max(case when rn = 1 then question end) question1,
  max(case when rn = 1 then answer end) answer1,
  max(case when rn = 2 then question end) question2,
  max(case when rn = 2 then answer end) answer2,
  max(case when rn = 3 then question end) question3,
  max(case when rn = 3 then answer end) answer3
from
(
  select id, question, answer,
    row_number() over(partition by id order by id, question) rn
  from yt
) src
group by id;

参见带有演示的SQL Fiddle

另一个建议是同时使用UNPIVOT和PIVOT函数来获得结果。UNPIVOT将使用您的question和answer列，并将它们转换为多行。

UNPIVOT的基本语法为：

select id,
  col+cast(rn as varchar(10)) col,
  value
from
(
  -- when you perform an unpivot the datatypes have to be the same. 
  -- you might have to cast the datatypes in this query
  select id, question, cast(answer as varchar(500)) answer,
    row_number() over(partition by id order by id, question) rn
  from yt
) src
unpivot
(
  value
  for col in (question, answer)
) unpiv;

参见演示。结果如下：

|      ID |       COL |                                VALUE |
--------------------------------------------------------------
| 4482515 | question1 | I would like to be informed by mail. |
| 4482515 |   answer1 |                                   No |
| 4482515 | question2 |                    Plan to Purchase? |
| 4482515 |   answer2 |                       Over 12 months |
| 4482515 | question3 |                   Test Question Text |
| 4482515 |   answer3 |                          some Answer |

如您所见，我row_number()在初始子查询中添加了一个值，以便您可以将每个答案与该问题相关联。一旦取消透视，您就可以将结果与question/answer连为一体的行号值在新的列名称上进行透视。具有PIVOT语法的代码将是：

select id, question1, answer1, question2, answer2,
  question3, answer3
from
(
  select id,
    col+cast(rn as varchar(10)) col,
    value
  from
  (
  -- when you perform an unpivot the datatypes have to be the same. 
  -- you might have to cast the datatypes in this query
    select id, question, cast(answer as varchar(500)) answer,
      row_number() over(partition by id order by id, question) rn
    from yt
  ) src
  unpivot
  (
    value
    for col in (question, answer)
  ) unpiv
) d
pivot
(
  max(value)
  for col in (question1, answer1, question2, answer2,
              question3, answer3)
) piv;

请参阅带有演示的SQL
Fiddle
。现在，根据您的情况，您表示将有数量众多的问题/答案。如果是这种情况，那么您将需要使用动态SQL来获取结果：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10))) 
                    from 
                    (
                      select row_number() over(partition by id 
                                               order by id, question) rn
                      from yt
                    ) d
                    cross apply
                    (
                      select 'question' col, 1 sort union all select 'answer', 2
                    ) c
                    group by col, rn, sort
                    order by rn, sort
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, ' + @cols + '
              from
              (
                select id,
                  col+cast(rn as varchar(10)) col,
                  value
                from
                (
                 -- when you perform an unpivot the datatypes have to be the same. 
                 -- you might have to cast the datatypes in this query
                  select id, question, cast(answer as varchar(500)) answer,
                    row_number() over(partition by id order by id, question) rn
                  from yt
                ) src
                unpivot
                (
                  value
                  for col in (question, answer)
                ) unpiv
              ) d
              pivot 
              (
                  max(value)
                  for col in (' + @cols + ')
              ) p '

execute(@query);

请参阅带有演示的SQL Fiddle。这些给出了结果：

|      ID |                            QUESTION1 | ANSWER1 |         QUESTION2 |        ANSWER2 |          QUESTION3 |     ANSWER3 |
------------------------------------------------------------------------------------------------------------------------------------
| 4482515 | I would like to be informed by mail. |      No | Plan to Purchase? | Over 12 months | Test Question Text | some Answer |