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

来源：互联网发布：淘宝酒编辑：程序博客网时间：2024/05/22 05:29

突然间发现，已经好久没有写博客了，也好久没用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)

0 0