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

Answer 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