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
