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.

You can look for this http://stackoverflow.com/questions/23066846/how-do-i-use-pivot-on-this-data/23067210#23067210 — Shantanu Gupta, Apr 18 '14 at 17:58

score 2 · Accepted Answer · answered Apr 18 '14 at 17:52

2

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)

answered Apr 18 '14 at 17:52

dean

9,960
2
25
26

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? – andrew.reynolds81 Apr 18 '14 at 18:04
No, you can't create a wiev from this. You will have to apply this to each query. – dean Apr 18 '14 at 18:10
Could I break the #t1 / #t2 out into seperate ctes? – andrew.reynolds81 Apr 18 '14 at 18:13
They're used in two separate select statements, one of them in dynamic scope, so you can't – dean Apr 18 '14 at 18:15
@dean - 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))` – Erran Morad Apr 18 '14 at 18:37
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. – dean Apr 18 '14 at 18:46
@dean - why do you say it is unsupported ? – Erran Morad Apr 20 '14 at 18:10
Check this: http://support.microsoft.com/kb/q287515. And this: http://www.sqlservercentral.com/Forums/Topic942789-1299-2.aspx – dean Apr 20 '14 at 18:22

score 0 · Answer 2 · edited May 23 '17 at 10:32

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

How my tables are related to @dean's tables - Question = #t1. Answers = #t2. — Erran Morad, Apr 18 '14 at 18:29

Transpose / Pivot Rows to Columns in SQL Server

2 Answers2

Linked