合 MSSQL行转列
Tags: MSSQLSQL Server行转列
一、 构造测试数据
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]; |