合 MSSQL行转列
Tags： MSSQLSQL Server行转列
👉 本文共约765个字，系统预计阅读时间或需3分钟。
一、 构造测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | -- 创建测试表 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U')) DROP TABLE [dbo].[TestRows2Columns] GO CREATE TABLE [dbo].[TestRows2Columns]( [Id] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) NULL, [Subject] [nvarchar](50) NULL, [Source] [numeric](18, 0) NULL ) ON [PRIMARY] GO -- 插入测试数据 INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source]) SELECT N'张三',N'语文',60 UNION ALL SELECT N'李四',N'数学',70 UNION ALL SELECT N'王五',N'英语',80 UNION ALL SELECT N'王五',N'数学',75 UNION ALL SELECT N'王五',N'语文',57 UNION ALL SELECT N'李四',N'语文',80 UNION ALL SELECT N'张三',N'英语',100 GO SELECT * FROM [TestRows2Columns]; |
二、 静态实现行转列
1 2 3 4 5 6 7 | -- 1：静态拼接行转列 SELECT [UserName], SUM(CASE [Subject] WHEN '数学' THEN [Source] ELSE 0 END) AS '[数学]', SUM(CASE [Subject] WHEN '英语' THEN [Source] ELSE 0 END) AS '[英语]', SUM(CASE [Subject] WHEN '语文' THEN [Source] ELSE 0 END) AS '[语文]' FROM [TestRows2Columns] GROUP BY [UserName]; |
三、 动态实现行转列
这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本。
1 2 3 4 5 6 7 8 9 | -- 2：动态拼接行转列 DECLARE @sql VARCHAR(8000) SET @sql = 'SELECT [UserName],' SELECT @sql = @sql + 'SUM(CASE [Subject] WHEN '''+[Subject]+''' THEN [Source] ELSE 0 END) AS '''+QUOTENAME([Subject])+''',' FROM (SELECT DISTINCT [Subject] FROM [TestRows2Columns]) AS a SELECT @sql = LEFT(@sql,LEN(@sql)-1) + ' FROM [TestRows2Columns] GROUP BY [UserName]' PRINT(@sql) EXEC(@sql) GO |
四、 透视函数 PIVOT
1. 静态方式
2005之后有了一个专门的PIVOT 和 UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的
1 2 3 4 5 6 7 8 9 10 | -- 3：静态PIVOT行转列 SELECT * FROM ( SELECT [UserName] , [Subject] , [Source] FROM [TestRows2Columns] ) p PIVOT ( SUM([Source]) FOR [Subject] IN ( [数学],[英语],[语文] ) ) AS pvt ORDER BY pvt.[UserName]; GO |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
