mssql数据行转列的技巧（mssql 行转列）

MSSQL 数据行转列的技巧

SQL脚本中，我们常常会遇到需要将行数据转换成列数据的情况。 这比较常见的SQL操作，是解决很多实际问题的重要技巧，如分析物流或业务数据及制作表格等应用。

在MSSQL中，常见的技巧有：PIVOT，UNPIVOT，MAX/CASE，row_number()等，各有特性，我们来一一介绍：

1、PIVOT

使用pivot技巧，可以将行转成列，实现将行数据转换成列数据，SQL脚本如下：

Select * From

(

Select * From TABLE_A

)p

Pivot

(

Sum(VALUE) For [Project] In ([A],[B],[C],[D])

)as HerePivot

简单说明：

Select * From TABLE_A 语句是用来查询出要转的数据，

pivot是把该表的“项目”列，按照A,B,C,D做行转列处理，

Sum(VALUE) For [Project]中，VALUE 是数据行值，[Project] 是所要转换的列名。

2、UNPIVOT

使用 unpivot 技巧，可以将列转成行，实现将列数据转换成行数据，SQL脚本如下：

Select S.name, E.Project,E.VALUE

From

(

Select * From TABLE_A

)X

Unpivot

(

VALUE For Project In([A]，[B]，[C]，[D])

)As E

Inner Join TABLE_S As S On E.id = S.id

简单说明：

Select * From TABLE_A 语句是用来查询出要转的数据，

Unpivot是把该表的A,B,C，D等列，按照项目(Project)做列转行处理，

VALUE For Project In([A]，[B]，[C]，[D])中，VALUE 是数据值，[Project] 是所要转换的列名。

3、MAX/CASE

使用MAX/CASE 技巧，可以将行转成列，实现将行数据转换成列数据，SQL脚本如下：

SELECT

id,

MAX(CASE WHEN [Type] = ‘A’ THEN [Value] END) AS A,

MAX(CASE WHEN [Type] = ‘B’ THEN [Value] END) AS B,

MAX(CASE WHEN [Type] = ‘C’ THEN [Value] END) AS C,

MAX(CASE WHEN [Type] = ‘D’ THEN [Value] END) AS D

FROM TABLE_A

GROUP BY id

简单说明：

SELECT * FROM TABLE_A 语句是用来查询出要转的数据，

MAX(CASE WHEN [Type] = ‘A’ THEN [Value] END) AS A， type 是所要转换的行名，VALUE是数据值。最后的Group by id 是将 id相同的汇总到一行中。

4、row_number()

row_number()技术也可以实现行转列，具体脚本如下：

SELECT

id,

MAX(CASE WHEN ROW_NO=1 THEN [Value] END) AS A,

MAX(CASE WHEN ROW_NO=2 THEN [Value] END) AS B,

MAX(CASE WHEN ROW_NO=3 THEN [Value] END) AS C,

MAX(CASE WHEN ROW_NO=4 THEN [Value] END) AS D

FROM

(

SELECT

row_number() over(partition by [id] order by [Type]) as ROW_NO,

[id],

[Type],

[Value]

FROM TABLE_A

) table_A

GROUP BY id

简单说明：

SELECT * FROM TABLE_A 语句是用来查询出要转的数据，

row_number()判断行排序，Max(CASE WHEN ROW_NO=1 THEN [Value] END)列转行，TYPE为行名，VALUE为数据值，最后Group by id 是将 id相同的汇总到一行中。

5、dynamic SQL

动态SQL 技术也可以实现行转列，通过动态拼接字符串，具体脚本如下：

declare @sql varchar(1000)

select @sql=’select name,’+stuff((

select ‘,’+quotename(project)

from Table

group by project

FOR XML PATH (”)),1,1,”)+’

from table

PIVOT

(

sum(value)

FOR project IN (‘+stuff((

select distinct ‘,’+quotename(project)

from info

group by project

FOR XML PATH (”)),1,1,”)+’)

)AS HerePivot’

–print(@sql)

exec(@sql)

简单说明：

通过select ‘,’+quotename(project) 组合要行转列的列名，stuff()函数用来去掉,前面的一个，通过pivot 实现行转列，exec(@sql)动态执行sql 语句，实现动态转换。

总结：

MSSQL 数据行转列的技巧，目的是将行数据转换成列数据，PIVOT/UNPIVOT/MAX/CASE/row_number()/dynamic SQL