Pivot

测试数据

Create Table TPivot
(
    ID int identity(1,1),
    ProductName Varchar(20),
    SalesMonth int,
    SalesCount int
)

insert into TPivot 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)

数据

Pivot语法

/****
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,SalesMonth, SalesCount From TPivot
) As Sales
Pivot
(
    Sum(SalesCount)
    For SalesMonth In ([1],[2],[3],[4],[5],[6])
) As PivotBL

效果