SQL 2008行列转换的pivot--产生动态列



突然间发现，已经好久没有写博客了，也好久没用SQLServer进行开发了。由于目前项目开发的原因，对于SQL Server才重视起来。发现SQL Server也引进了不少新的东西，现将一个不错的动态行列转换的例子给大家分享一下，行列转换的函数为：pivot,unpivot

转自：  http://www.2cto.com/database/201209/153233.html

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)