SQL Server:具有自定义列名称的数据透视 [英] SQL Server : Pivot with custom column names
问题描述
我需要使用自定义列名来旋转表.请参阅下面的表格格式.
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提琴
另一个建议是同时使用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提琴.现在，根据您的情况，您表示将有数量众多的问题/答案.如果是这种情况，那么您将需要使用动态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提琴.这些给出结果:
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 |
这篇关于SQL Server:具有自定义列名称的数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
