SQL2008 行列转换的 pivot
IF OBJECT_ID('tempdb..#ABC') IS NOT NULLDROP TABLE #ABCcreate table #ABC(ID INT,UserID BIGINT ,UserExamID INT ,TestPaperID INT ,QuestionID INT,AnswerID INT,Ctime DATETIME)INSERT INTO #ABCSELECT 1,120629210042331600,1,3,22,49,GETDATE() UNION ALLSELECT 2,120629210042331600,1,3,23,51,GETDATE() UNION ALLSELECT 3,120629210042331600,1,3,24,56,GETDATE() UNION ALLSELECT 4,120629210042331600,1,3,25,62,GETDATE() UNION ALLSELECT 5,120629210042331600,1,3,26,66,GETDATE() UNION ALLSELECT 6,120629210042331600,1,3,27,72,GETDATE() UNION ALLSELECT 7,120629210042331600,1,3,28,77,GETDATE() UNION ALLSELECT 8,120629210042331600,1,3,29,81,GETDATE() UNION ALLSELECT 9,120629210042331600,1,3,30,86,GETDATE() UNION ALLSELECT 10,120629210042331600,1,3,31,90,GETDATE() UNION ALLSELECT 1,120629210011732588,1,3,22,49,GETDATE() UNION ALLSELECT 2,120629210011732588,1,3,23,51,GETDATE() UNION ALLSELECT 3,120629210011732588,1,3,24,56,GETDATE() UNION ALLSELECT 4,120629210011732588,1,3,25,62,GETDATE() UNION ALLSELECT 5,120629210011732588,1,3,26,66,GETDATE() UNION ALLSELECT 6,120629210011732588,1,3,27,72,GETDATE() UNION ALLSELECT 7,120629210011732588,1,3,28,77,GETDATE() UNION ALLSELECT 8,120629210011732588,1,3,29,81,GETDATE() UNION ALLSELECT 9,120629210011732588,1,3,30,86,GETDATE() UNION ALLSELECT 10,120629210011732588,1,3,31,90,GETDATE() SELECT * FROM #ABC
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 tpivot(max(t.AnswerID)for t.QuestionID in ([email protected]+')) as r' EXEC( @sql)
