SQL 2008行列转换的pivot
[sql]
IF OBJECT_ID('tempdb..#ABC') IS NOT NULL
DROP TABLE #ABC
create table #ABC
(
ID INT
,UserID BIGINT
,UserExamID INT
,TestPaperID INT
,QuestionID INT
,AnswerID INT
,Ctime DATETIME
)
INSERT INTO #ABC
SELECT 1,120629210042331600,1,3,22,49,GETDATE() UNION ALL
SELECT 2,120629210042331600,1,3,23,51,GETDATE() UNION ALL
SELECT 3,120629210042331600,1,3,24,56,GETDATE() UNION ALL
SELECT 4,120629210042331600,1,3,25,62,GETDATE() UNION ALL
SELECT 5,120629210042331600,1,3,26,66,GETDATE() UNION ALL
SELECT 6,120629210042331600,1,3,27,72,GETDATE() UNION ALL
SELECT 7,120629210042331600,1,3,28,77,GETDATE() UNION ALL
SELECT 8,120629210042331600,1,3,29,81,GETDATE() UNION ALL
SELECT 9,120629210042331600,1,3,30,86,GETDATE() UNION ALL
SELECT 10,120629210042331600,1,3,31,90,GETDATE() UNION ALL
SELECT 1,120629210011732588,1,3,22,49,GETDATE() UNION ALL
SELECT 2,120629210011732588,1,3,23,51,GETDATE() UNION ALL
SELECT 3,120629210011732588,1,3,24,56,GETDATE() UNION ALL
SELECT 4,120629210011732588,1,3,25,62,GETDATE() UNION ALL
SELECT 5,120629210011732588,1,3,26,66,GETDATE() UNION ALL
SELECT 6,120629210011732588,1,3,27,72,GETDATE() UNION ALL
SELECT 7,120629210011732588,1,3,28,77,GETDATE() UNION ALL
SELECT 8,120629210011732588,1,3,29,81,GETDATE() UNION ALL
SELECT 9,120629210011732588,1,3,30,86,GETDATE() UNION ALL
SELECT 10,120629210011732588,1,3,31,90,GETDATE()
SELECT * FROM #ABC
[sql]
DECLARE @s NVARCHAR(4000)
SELECT @s = ISNULL(@s + ',', '') + QUOTENAME(QuestionID)
FROM (select distinct QuestionID from #ABC) as A ---列名不要重复
Declare @sql NVARCHAR(4000)
SET @sql='
select r.* from
(select UserID,QuestionID,AnswerID from #ABC) as t
pivot
(
max(t.AnswerID)
for t.QuestionID in ('+@s+')
) as r'
EXEC( @sql)
