如何在列中使用可变数据透视表?

All,

所有人,

rownum  respnum   q2        q3
-----------------------------------
1       33        Missy     155-4
2       46        Melissa   55-98
3       73        Emma      998-4

How would you PIVOT the above table to show the desired results below? Keep in mind the original column headers are static (q2,q3,...) but the answers can vary.

如何将上面的表调到轴上以显示下面所需的结果?记住，最初的列标题是静态的(q2,q3，…)，但是答案是不同的。

 rownum respnum   question  answer
-----------------------------------
1       33        q2        Missy
1       33        q3        155-4
2       46        q2        Melissa 
2       46        q3        55-98
3       73        q2        Emmat
3       73        q3        998-4

Thanks

谢谢

2 个解决方案

#1

UnPivot would be more performant, but if you don't want to specify all the fields, consider the following:

如果您不想指定所有的字段，那么可以考虑下面的方法:

Example

例子

Select rownum
      ,respnum
      ,C.*
 From YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select question = a.value('local-name(.)','varchar(100)')
                      ,answer   = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('rownum','respnum')
             ) C

Returns

rownum  respnum question    answer
1       33      q2          Missy
1       33      q3          155-4
2       46      q2          Melissa
2       46      q3          55-98
3       73      q2          Emma
3       73      q3          998-4

#2

Using UNPIVOT, we can do this like:

使用UNPIVOT，我们可以这样做:

select * from 
(select 
    rownum, 
    respnum, 
    Q2=Cast(q2 as varchar(max)),
    Q3=cast(Q3 as varchar (max)) 
 from sample)src
unpivot
( answer for question in ([q2],[q3]))up

see working demo

查看演示工作

#1