How to PIVOT table with variable data in columns

Question

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.

 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

John Cappelletti · Accepted Answer · 2018-03-30T20:49:35.663

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

@J.Pyz Happy to help. Yes we mix a little XML in there. I tend to use this technique for discovery or if I need to unpivot a smaller sample — John Cappelletti, Mar 30 '18 at 21:02

DhruvJoshi · Answer 2 · 2018-03-30T21:23:35.477

1

Using UNPIVOT, we can do this like:

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

edited Mar 30 '18 at 21:23

answered Mar 30 '18 at 21:04

DhruvJoshi

17,041
6
41
60

1

My first thougt as well. However, "answers can vary" indicated (to me) variable data types – John Cappelletti Mar 30 '18 at 21:13
Edited my answer to accommodate the varying data types – DhruvJoshi Mar 30 '18 at 21:24
May not have been necessary given the small sample. You should add back your original +1 – John Cappelletti Mar 30 '18 at 21:26

How to PIVOT table with variable data in columns

2 Answers2