isnull pivot server sql_Sqlserver中PIVOT行转列透视操作

创建表：

IF OBJECT_ID(‘T040_PRODUCT_SALES‘) IS NOT NULL

DROP TABLE T040_PRODUCT_SALES

create table T040_PRODUCT_SALES

(

ID INT IDENTITY(1,1),

ProductName VARCHAR(20),

SaleMonth INT,

SalesCount INT

)

插入数据并排序：

INSERT INTO T040_PRODUCT_SALES VALUES

(‘Bicycle‘,1,1),

(‘Shoes‘,2,2),

(‘Clothes‘,3,3),

(‘Books‘,4,4),

(‘Medicine‘,5,5),

(‘Drinks‘,6,6),

(‘Shoes‘,7,7),

(‘Books‘,1,2),

(‘Bicycle‘,1,3),

(‘Medicine‘,1,4),

(‘Clothes‘,1,5),

(‘Mobile Phone‘,1,6),

(‘Books‘,1,7),

(‘Medicine‘,1,8),

(‘Shoes‘,1,9),

(‘Bicycle‘,2,10)

SELECT ProductName,

SaleMonth,

SUM(SalesCount) AS SalesCount

FROM T040_PRODUCT_SALES

GROUP BY ProductName,

SaleMonth

ORDER BY ProductName,

SaleMonth

格式：

/****

SELECT 非透视列,

[透视列 1] AS ‘列名1‘,

[透视列 2] AS ‘列名2‘,

[透视列 3] AS ‘列名3‘

FROM (

-- 源数据

SELECT 非透视列,

透视列值的来源列,

需要聚合的值

FROM 表

)AS 别名

PIVOT

(

SUM(需要聚合的值)

FOR 透视列值的来源列 IN ([透视列 1]，[透视列 2]，[透视列 3])

)AS 别名

****/

行转列的代码：

select ProductName,

ISNULL([1],0) AS ‘1‘,

ISNULL([2],0) AS ‘2‘,

ISNULL([3],0) AS ‘3‘,

ISNULL([4],0) AS ‘4‘,

ISNULL([5],0) AS ‘5‘,

ISNULL([6],0) AS ‘6‘ from (

select ProductName,

SaleMonth,

SalesCount from T040_PRODUCT_SALES)

as Sales

pivot

(

SUM(SalesCount)

FOR SaleMonth IN([1],[2],[3],[4],[5],[6])

) as PIVOTBL

结果：

Sqlserver中PIVOT行转列透视操作

标签：arch   values   esc   代码   来源   entity   ges   int   建表

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/sunliyuan/p/6595977.html