Transpose / Pivot Rows to Columns in SQL Server

Question

I have three tables - the first contains questions with a unique ID

QID     | Question  
------------------  
1       | Question 1  
2       | Question 2  
3       | Question 3  
4       | Question 4  
5       | Question 5  

The second contains answers to those questions, the unique ID from the first table and then an ID of the record that links the answers together

QID     | Answer | Parent
-------------------------
1       | Yes    | 123
2       | No     | 123  
3       | No     | 123  
4       | Yes    | 123  
5       | Yes    | 123
1       | No     | 124
2       | Yes    | 124  
3       | No     | 124  
4       | No     | 124  
5       | No     | 124

The third contains other information about the parent

Parent     |      Parent Title
----------------------------------------
123        |      Questionnaire Sample 1
124        |      Questionnaire Sample 2

Ideally I would like to create a view that transposes or pivots the data into a more usable format, I'm thinking something along the lines of:

Parent     | Question 1     | Question 2     | Question 3     | Question 4     | Question 5
123        | Yes            | No             | No             | Yes            | Yes
124        | No             | Yes            | No             | No             | No

I found a couple of really nice threads on here that I've been working with:

Simple way to transpose columns and rows in Sql?

T-SQL Pivot/Unpivot(Transpose) Column Headers Needed as Data Rows

There was another one:

Transpose rows into columns in SQL Server 2008 R2

that I pulled the following from:

SELECT [First_Set], [Second_Set]
FROM
(
  SELECT B.ColumnName, A.Value
    , row_number() over(partition by a.Table2ID
                    order by a.Value) seq
  FROM Table_1 AS A 
  INNER JOIN Table_2 AS B 
    ON A.Table2ID = B.ID
) AS P
PIVOT
(
  min(P.Value)
  for P.ColumnName in ([First_Set], [Second_Set])
) AS PIV;

I then modified that to be:

SELECT [PARENT], [QUESTION], [ANSWER]
FROM
(
  SELECT B.PARENT_TITLE, C.QUESTION, A.ANSWER
    , row_number() over(partition by a.PARENT
                        order by a.PARENT) seq
  FROM answerTable AS A 
  INNER JOIN parentTable AS B 
    ON A.PARENT = B.PARENT
  INNER JOIN questionTable AS C 
    ON A.QID = C.QID    
) AS P
PIVOT
(
  min(P.RESULT)
  for P.PARENT in ([PARENT], [QUESTION], [ANSWER])
) AS PIV;

Which returns but still isn't as I need it.

It's not feasible for me to create new tables so ideally I'm looking for a dynamic means here, it's for reporting so a view would be easiest / best in my opinion but I am open to suggestions.

Thanks.

Gamophyllous

You can look for this #23067346 – Eyeglasses

Answer 1

Here's how to do it dynamically:

create table #t1(QID int, Question char(10))
insert #t1 values
(1, 'Question 1'),  
(2, 'Question 2'),  
(3, 'Question 3'),  
(4, 'Question 4'),  
(5, 'Question 5')  

create table #t2 (QID int, Answer char(3), Parent int)
insert #t2 values 
(1,        'Yes',    123),
(2,        'No ',    123),  
(3,        'No ',    123),  
(4,        'Yes',    123),  
(5,        'Yes',    123),
(1,        'No ',    124),
(2,        'Yes',    124),  
(3,        'No ',    124),  
(4,        'No ',    124),  
(5,        'No ',    124)

declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(Question) 
            FROM #t1 -- your table here
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
select @collist

declare @q nvarchar(max)
set @q = '
select * 
from (
    select 
    Question, Answer, Parent
        from (
        select #t1.*, #t2.Answer, #t2.parent
        from #t1
        inner join #t2 on #t1.QID = #t2.QID
    ) as x
) as source
pivot (
    max(Answer)
    for Question in (' + @collist + ')
) as pvt
'

exec (@q)

Rubbish

Wow! OK thats awesome - I was able to put my real table names in and get that working and returning data for me. Thank you! Am I able to build that into a view though? I've only done basic view building so far... can my view dynamically build the temp tables each time? – Gamophyllous

No, you can't create a wiev from this. You will have to apply this to each query. – Rubbish

Could I break the #t1 / #t2 out into seperate ctes? – Gamophyllous

They're used in two separate select statements, one of them in dynamic scope, so you can't – Rubbish

@Rubbish - Is there any advantage of using for xml path over the the code that I used to create a column list ? I wonder why people use xml path. select @pivotList = @pivotList + '[' + Question + '],' from Questions set @pivotList = SUBSTRING(@pivotList, 0, LEN(@pivotList)) – Pendulous

The fact that the code you used worked is an unwanted side-effect of something else and is not supported by MS, meaning that it might stop working in some future version of SQL Server. Also, the order is not guaranteed. Unsupported alone is a strong enough argument for me. – Rubbish

@Rubbish - why do you say it is unsupported ? – Pendulous

Check this: support.microsoft.com/kb/q287515. And this: sqlservercentral.com/Forums/Topic942789-1299-2.aspx – Rubbish

Answer 2

Another way of solving your question -

declare @dynamicSQL varchar(max) = ''
declare @pivotList varchar(max) = ''
declare @uniqueQuestions table ([Question] varchar(50) )
insert into @uniqueQuestions
select distinct [Question] from Questions 
select @pivotList = @pivotList + '[' + Question + '],'  from @uniqueQuestions
set @pivotList = SUBSTRING(@pivotList, 0, LEN(@pivotList))
set @dynamicSQL = 
'select *
from
(
select Parent, Question, Answer
from Questions as q
left join Answers as a
on q.QID = a.QID
) as src
pivot(
max(answer)
for Question in (' + @pivotList + ')' + 
')as pvt'
exec(@dynamicSQL)

Thanks to this post for making it happen - Convert multiple rows into one with comma as separator

Pendulous

How my tables are related to @dean's tables - Question = #t1. Answers = #t2. – Pendulous