SQL Server:使用自定义列名进行数据透视

2022-01-22 00:00:00 pivot sql tsql sql-server

我需要使用自定义列名来旋转表格.请看下表格式.

I need to pivot the Table with custom column name's. Please see the table format below.

当前格式:

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

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

I would like to display the Data in the following format.

所需格式:

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 应该是动态生成的.

Please note: I don't know about the number of questions and answers in a row so the column's question1 Answer1 should be dynamically generated.

谢谢

感谢您的帮助，我尝试一下您提供给我的动态代码并收到此错误.

Thank you for your help, I give it a try for the dynamic code you provide it to me and getting this error.

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，结果如下.

I Print out the SQL and the result is below.

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 代码也在下面

My original SQL code is below as well

    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);

等待您的帮助！

推荐答案

有几种方法可以做到这一点.

There are a few ways that you can do this.

如果您有已知数量的问题/答案，那么您可以使用 row_number() 以及聚合函数和 CASE 表达式:

If you had a known number of questions/answers then you could use row_number() along with an aggregate function and a CASE expression:

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 with Demo

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

Another suggestion would be to use both the UNPIVOT and the PIVOT function to get the result. The UNPIVOT will take your question and answer columns and convert them into multiple rows.

UNPIVOT 的基本语法是:

The basic syntax for the UNPIVOT will be:

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;

参见演示.这给出了一个结果:

See Demo. This gives a result:

|      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 语法的代码将是:

As you can see, I added a row_number() value to the initial subquery so you can associate each answer to the question. Once this has been unpivoted, then you can pivot the result on the new column names with the question/answer with the concatenated row number value. The code with the PIVOT syntax will be:

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 with Demo.现在在你的情况下，你说你将有一个动态数量的问题/答案.如果是这种情况，那么您将需要使用动态 SQL 来获取结果:

See SQL Fiddle with Demo. Now in your situation, you stated that you will have a dynamic number of questions/answers. If that is the case, then you will need to use dynamic SQL to get the result:

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 with Demo.这些给出了一个结果:

See SQL Fiddle with Demo. These give a result:

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

相关文章