SQL 行转列（二维） 总结

最近在写一个销售报表的SQL，对此类型的二维SQL 进行一个总结。

表结构 Test

ProductName SaleMonth SaleNumber

A                 m1            1

B                 m1            1

A                 m2            2

B                 m2            3

要求出查询结果：

ProductName M1 M2

A                 1    2

B                 1    3

方法一：

创建临时表，通过游标将数据添加到临时表中。

但是此方法只适合列固定，如果列不确定，编写起来比较麻烦。所以此方法忽略。

方法二：

思路：通过Case依次创建列。

如下： (Case SaleMonth When 'M1' then SaleNumber ELSE 0 END) [M1],

(Case SaleMonth When 'M2' then SaleNumber ELSE 0 END) [M2]

带来的问题是：如何获取不确定的列名。

declare @sql varchar(8000)

SET @sql='SELECT ProductName' 

-- 这步关键需要初始化，否则会无效。因为varchar类型相加，如果有个内容为空，则相加之后的内容也是为空

SELECT @sql = @sql  + ',(Case SaleMonth WHEN ''' + SaleMonth 

+ ''' THEN SaleNumber ELSE 0 END) [' 

+ SaleMonth + ']' FROM (SELECT DISTINCT SaleMonth FROM Test) A

SET @sql = @sql + ' FROM Test'

PRINT @sql

EXEC(@sql)

运行结果如下：

p m1 m2 m3 m4

A 1 0 0 0
A 0 2 0 0
A 0 0 3 0
A 0 0 0 4
B 1 0 0 0
B 0 2 0 0
B 0 0 3 0
B 0 0 0 4

这并不是所预期的。经分析，在进行Case处理的时候加上MAX可避免。

如下：

declare @sql varchar(8000)

SET @sql='SELECT ProductName' 

-- 这步关键需要初始化，否则会无效。因为varchar类型相加，如果有个内容为空，则相加之后的内容也是为空

SELECT @sql = @sql  + ',MAX(Case SaleMonth WHEN ''' + SaleMonth 

+ ''' THEN SaleNumber ELSE 0 END) [' + SaleMonth + ']' FROM 

(SELECT DISTINCT SaleMonth FROM Test) A

SET @sql = @sql + ' FROM Test GROUP BY ProductName'

PRINT @sql

EXEC(@sql)

运行结果如下：

P M1 M2 M3 M4

A 1 2 3 4
B 1 2 3 4

效果达到预期。

但是带来了另外一个问题，如果SaleNumber中有负数的时候则会出现数据错误（此列数据会被0代替）。此时需要根据实际情况进行相应的更改。

方法三：

SQL2005中有  PIVOT函数可实现行转列

PIVOT具体介绍可以参考：http://technet.microsoft.com/zh-cn/library/ms177410.aspx

SQL如下：

SELECT * FROM (SELECT ProductName, SaleNumber, SaleMonth FROM Test) A 

PIVOT(MAX(SaleNumber) FOR SaleMonth IN(m1,m2,m3,m4)) B

带来的问题是：如何动态生成M1，M2。。。

declare @sql varchar(8000)

SELECT @sql = ISNULL(@sql + ',', '') + SaleMonth FROM Test GROUP BY SaleMonth

SET @sql = 'SELECT * FROM (SELECT ProductName, SaleNumber, SaleMonth FROM Test) A 

PIVOT(MAX(SaleNumber) FOR SaleMonth IN(' + @sql + ')) B'
PRINT @sql

EXEC(@sql)

总结：如果你的表中有主键或者某一列具备唯一值时，在进行创建源的时候记得将此列排除。