MSSQL横列转纵列

   在工作中我们一般会遇到将横列转成纵列的需求，下面是横列转纵列的案例：

1.建表

CREATE TABLE [dbo].[AcrossChangeEndLong](  [Id] [INT] IDENTITY(1,1) NOT NULL,  [Name] [NVARCHAR](50) NOT NULL,  [Subject] [NVARCHAR](50) NOT NULL,  [Score] [INT] NOT NULL, CONSTRAINT [PK_AcrossChangeEndLong] PRIMARY KEY CLUSTERED (  [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[AcrossChangeEndLong] ADD CONSTRAINT [DF_AcrossChangeEndLong_Score] DEFAULT ((0)) FOR [Score]GO

2.具体SQL

SELECT Name AS '姓名',    MAX(CASE [Subject]       WHEN '语文' THEN Score       ELSE 0      END) AS '语文' ,    MAX(CASE [Subject]       WHEN '英语' THEN Score       ELSE 0      END) AS '英语' ,    MAX(CASE [Subject]       WHEN '数学' THEN Score       ELSE 0      END) AS '数学'FROM  dbo.AcrossChangeEndLongGROUP BY Name ORDER BY Name

另外在Server.aspx' >SQL Server 2005之后有了一个专门的PIVOT 和 UNPIVOT 关系运算符做行列之间的转换，具体SQL如下

SELECT *FROM  ( SELECT  Name AS '姓名',          Subject ,          Score     FROM   dbo.AcrossChangeEndLong    ) p PIVOT ( MAX(Score) FOR Subject IN ( [数学], [英语], [语文] ) ) AS pvtORDER BY pvt.姓名

具体效果：  

扩展：可能我们需要加一个统计行，我们可以用UNION ALL连接 上一个表的结果【可以将结果存入临时表】,再用函数SUM

原标题：MSSQL横列转纵列

关键词：MSSQL