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);

  普通的结果:金沙官网线上 1,要实现的效果:金沙官网线上 2

--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);

 金沙官网线上 3—》金沙官网线上 4

  需要将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的列转行,和行专列已经完成。有什么不对的地方,请大家指正。

本文由金沙官网线上发布于数据库,转载请注明出处:SQL列转行,行转列实现

您可能还会对下面的文章感兴趣: