Please find the table (OututTable) that needs to be transposed. Here the QuestionID is formed by concatenating two values -[Question:AnswerID]
refID | SessionID | QuestionID | AnswerValue
9000 | 205545715 | [4907] | Good morning
12251 | 205543469 | [10576:16307] | 3
12255 | 205543469 | [10907:17001] | 4
13157 | 205543703 | [10576:16307] | 3
14387 | 205543493 | [10907:17001] | 2
14389 | 205543493 | [10911:17007] | 3
The expected output should have one row per SessionID and the number of columns are dynamic
SessionID | [4097] | [10576:16307] | [10907:17001] | [10911:17007]
205545715 |Good morning | | |
205543469 | | 3 | 4 |
205543703 | | 3 | |
205543493 | | | 2 | 3
I have the output in the above format but there are only NULL values inserted instead of Answer values
I am thinking there might a mismatch in column names. Any help would be great! please let me know.
Code:
set @Questions = (STUFF((SELECT distinct ',[' + cast(i.SessionID as varchar(20)) + ']'
FROM OutputTable i
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, ''))
print @Questions
set @SQLQuery = 'select QuestionID,'+ @Questions +' from '+'('+ 'select SessionID,QuestionID,AnswerValue from OutputTable '+ ') p '+ 'PIVOT'+ '('+'max(Answervalue)'+'FOR p.SessionID IN ('+ @Questions +')' +') as pvt'
Great Question! The problem is with the brackets in the QuestionID. While these are necessary for the Pivot Column Aliases, these don't work as string filters.
The code sample also switches QuestionID and SessionID for the expected output.
This code will return the expected output, sorted slightly differently. A temp table is created here to simulate the OutputTable object. This will need to be switched out with the DB Table.
declare
@Questions varchar(max),
@SQLQuery varchar(max)
create table #OutputTable
(
refID int,
SessionID int,
QuestionID varchar(50),
AnswerValue varchar(50)
)
insert into #OutputTable
values
(9000,205545715,'[4907]','Good morning'),
(12251,205543469,'[10576:16307]','3'),
(12255,205543469,'[10907:17001]','4'),
(13157,205543703,'[10576:16307]','3'),
(14387,205543493,'[10907:17001]','2'),
(14389,205543493,'[10911:17007]','3')
set @Questions = (STUFF((SELECT distinct ',' + cast(i.QuestionID as varchar(20))
FROM #OutputTable i
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, ''))
set @SQLQuery = '
select SessionID,'+ @Questions +'
from (
select
SessionID,
replace(replace(QuestionID,''['',''''),'']'','''') QuestionID,
AnswerValue
from #OutputTable
) p
PIVOT (
max(Answervalue)
FOR p.QuestionID IN ('+ @Questions +')
) as pvt
order by SessionID desc'
exec(@SQLQuery)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments