SQL列转行，行转列实现

　　在工作中，大家可能会遇到一些SQL列转行、行转列的问题，恰好，我也遇到了，就在此记录一下。此处所用的是SQLServer2008R2。

　　行转列，列转行，都要预先知道要要处理多少数据，在此我就以三种方案来说明一下，分别是case when、PIVOT、For XML。

　　列转行：

　　首先创建测试数据：

--测试语句，准备创建表的语句：如下

CREATE TABLE dbo.test1

 (

   id       int             IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,

   typeid    nvarchar(50) NULL,

   typeName nvarchar(50) NULL,

   number   int          NULL

 );

CREATE UNIQUE CLUSTERED INDEX idx_id

ON dbo.test1(id);

--插入测试数据

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S1', '一班',50);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S1', '二班',80);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S1', '三班',60);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S2', '一班',50);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S2', '二班',30);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S2', '三班',60);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S3', '一班',70);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S3', '儿班',80);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S3', '三班',60);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S3', '四班',30);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S4', '一班',90);

INSERT INTO dbo.test1(typeid,typeName,number) VALUES('S4', '二班',50);

　　普通的结果：，要实现的效果：

--CASE WHEN

select

SUM(case when typeid='S1' then number end) as S1,

SUM(case when typeid='S2' then number end) as S2,

SUM(case when typeid='S3' then number end) as S3,

SUM(case when typeid='S4' then number end) as S4

from test1

--PIVOT

select *

from (select typeid,number from test1) as test

pivot(sum(number) for typeid in([S1],[S2],[S3],[S4]))as tbl

　　行转列：

　　首先创建测试数据：

--测试语句，准备创建表的语句：如下

CREATE TABLE dbo.test2

 (

   id       int             IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,

   typeid    nvarchar(50) NULL,

   typeName nvarchar(50) NULL,

   number   int          NULL,

   number2  int             NULL,

   number3  int             NULL

 );

CREATE UNIQUE CLUSTERED INDEX idx2_id

ON dbo.test2(id);

--插入测试数据

INSERT INTO dbo.test2(typeid,typeName,number,number2,number3) VALUES('S1', '一班',50,90,100);

INSERT INTO dbo.test2(typeid,typeName,number,number2,number3) VALUES('S2', '二班',20,64,30);

　—》

　　需要将number，number2，number3合并到val列，colName是合并之后的原始列名，val是合并之后原始的值。

--unpivot

select id,typeid,typeName,colName,val

from test2

unpivot(val for colName in([number],[number2],[number3]))as tbl

　　至此，SQL的列转行，和行专列已经完成。有什么不对的地方，请大家指正。