SQL2008 行列转换的 pivot

最新推荐文章于 2023-08-21 22:00:16 发布

wangxianshou

最新推荐文章于 2023-08-21 22:00:16 发布

阅读量714

点赞数

CC 4.0 BY-SA版权

分类专栏： MSSQL 文章标签： sql table insert object

本文链接：https://blog.youkuaiyun.com/wangxianshou/article/details/7946325

MSSQL 专栏收录该内容

27 篇文章

订阅专栏

本文详细介绍了如何在SQL Server 2008中使用Pivot函数进行行列转换，通过实例解析了将多行数据转换为列的过程，帮助读者掌握在数据库操作中灵活运用Pivot技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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)