Pivot Example when you don't know the exact values to Pivot on

Say, I have the following temporary table (@.tbl) where the QuestionID field will change values over time

Survey QuestionID Answer
1 1 1
1 2 0
2 1 1
2 2 2

I'd like to perform a pivot on it like this: select * from @.tbl Pivot (min(Answer) for QuestionID in ([1], [2])) as PivotTable

...however, I can't just name the [1], [2] values because they're going to change.

Instead of naming the values like this:
for QuestionID in ([1], [2], [3], [4])

I tried something like this:
for QuestionID in (select distinct QuestionID from @.tbl)

but am getting a syntax error. Is it possible to set up a pivot like this:
select * from @.tbl Pivot (min(Answer) for Question_CID in (select distinct @.QuestionID from @.tbl)) as PivotTable

or does anyone know another way to do it?

You cannot use a subquery in the IN for your pivot. I wish we could. Here is a dynamic solution:

Code Snippet

Create Table #tb1 (

Survey int,

QuestionID int,

Answer int

);

--Survey QuestionID Answer

--1 1 1

--1 2 0

--2 1 1

--2 2 2

--Sample Data

Insert Into #tb1 Values('1','1', '1');

Insert Into #tb1 Values('1','2', '0');

Insert Into #tb1 Values('2','1', '1');

Insert Into #tb1 Values('2','2', '2');

Insert Into #tb1 Values('3','3', '1');

--SELECT DISTINCT QuestionID FROM #tb1

SET

NOCOUNT ON

DECLARE

@.T AS TABLE(y nvarchar(20) NOT NULL PRIMARY KEY)

INSERT

INTO @.T SELECT DISTINCT QuestionID FROM #tb1

DECLARE

@.T1 AS TABLE(num int NOT NULL PRIMARY KEY)

DECLARE @.i AS int

SET @.i=1

WHILE @.i <20

BEGIN

INSERT INTO @.T1 SELECT @.i

SET @.i=@.i+1

END

DECLARE @.cols AS nvarchar(MAX), @.cols2 AS nvarchar(MAX),@.y AS nvarchar(20)

SET @.y = (SELECT MIN(y) FROM @.T)

SET @.cols = N''

SET @.cols2 = N''

WHILE @.y IS NOT NULL

BEGIN

SET @.cols = @.cols + N',['+CAST(@.y AS nvarchar(20))+N']'

SET @.cols2 = @.cols2 + N'+ coalesce(['+CAST(@.y AS nvarchar(20))+N'],'''')'

SET @.y = (SELECT MIN(y) FROM @.T WHERE y > @.y)

END

SET @.cols = SUBSTRING(@.cols, 2, LEN(@.cols))

SET @.cols2 = SUBSTRING(@.cols2, 2, LEN(@.cols2)-1)

DECLARE @.sql AS nvarchar(MAX)

SET @.sql = N'SELECT Survey, ' +@.cols + N' FROM (SELECT Survey, QuestionID, Answer FROM #tb1) as t

PIVOT (Min(Answer) FOR QuestionID IN(' + @.cols + N')) AS pvt'

EXEC sp_executesql @.sql

print @.sql

drop table #tb1

|||

Try:

Code Snippet

use tempdb

go

create table #t1 (

survey int,

questionid int,

answer int

)

insert into #t1 values(1,1, 1)

insert into #t1 values(1,2, 0)

insert into #t1 values(2,1, 1)

insert into #t1 values(2,2, 2)

insert into #t1 values(0,0, 1)

declare @.cols nvarchar(max)

declare @.sql nvarchar(max)

set @.cols = stuff(

(

select ',' + quotename(QuestionID)

from (select distinct QuestionID from #t1) as t

order by QuestionID

for xml path('')

), 1, 1, '')

set @.sql = N'

select

Survey, ' + @.cols + N'

from

#t1

pivot

(

min(Answer)

for QuestionID in (' + @.cols + N')

) as pvt

'

exec sp_executesql @.sql

drop table #t1

go

In case you do not want to see the NULL values, take a look to this post. There I posted a similar solution.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2123214&SiteID=1

Be careful with SQL Injection.

PIVOT on Steroids

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html

AMB