TSQL keep 3 columns and unpivot the other 3 into a single column

Question

using T-SQL, I have the following data:

ID     name     number    q1    q2    q3
---    -----    ------    --    --    -- 
1      paul     7777      yes   no    maybe
2      steve    8786      no    yes   definitely

and I am looking to unpivot it so that it represents:

ID    name      number    question   answer  
--    ----      -----     --------   ------
1     paul      7777      Q1         yes
1     paul      7777      Q2         no
1     paul      7777      Q3         maybe
2     steve     8786      Q1         no
2     steve     8786      Q2         yes
2     steve     8786      Q3         definitely

so far I have managed to unpivot the id, name, number and question parts, but cannot get the answer to complete accordingly.

I have used:

select [name],[number],[id],[question_number] from (select [name],[number],[id],
[q1],[q2],[q3]) unpivot
(something for [question_number] in ([Q1],{Q2],[Q3])) as unpvt

This is obviously a simplified version of my data, but the requirement is still the same. Can anyone help please?

Thanks.

Answer 1

My first answer :)

Without pivot:

select ID,name,number,'Q1' as question ,Q1 as answer    from #yourtable
union all select ID,name,number,'Q2',Q2  from #yourtable
union all select ID,name,number,'Q3',Q3  from #yourtable

Here the full example

create table #yourtable (
    ID int, 
    name nvarchar(20), 
    number int,    
    q1  nvarchar(20),  
    q2 nvarchar(20),
    q3 nvarchar(20));
insert into #yourtable values(1 ,'paul', 7777,'yes','no','maybe');
insert into #yourtable values(2, 'steve', 8786, 'no', 'yes', 'definitely');

select ID,name,number,'Q1' as question ,Q1 as answer    from #yourtable
union all select ID,name,number,'Q2',Q2  from #yourtable
union all select ID,name,number,'Q3',Q3  from #yourtable