源数据库:
转换后:
要点:
1, 如果原数据有多余的列,先剔除。(要实现行转列,就必定有关分组,一组内的才可行转列,否则如果有多余的列,这些列也会自动作为分组条件),结果如下样子:
SELECT t.* FROM T_partition
PIVOT(COUNT(result) FOR RESULT IN ([胜],[负])) AS t
ORDER BY t.Date
2,Pivot关键字 FOR 列名,就是按某列执行聚合,可以是Count(),Sum(),AVG()…,是什么就在Pivot后写什么。
如果对源数据就进行了聚合查询,再PIVOT就不是想要的结果了:
WITH xx AS(
SELECT Date,Result
FROM dbo.T_partition
GROUP BY Date,Result
)
SELECT t.* FROM xx
PIVOT(COUNT(result) FOR RESULT IN ([胜],[负])) AS t
ORDER BY t.Date
Pivot示例:
INSERT INTO [dbo].[T_partition]
([Date]
,[Result])
VALUES
(DATEADD(MONTH,2, GETDATE()),'胜')
INSERT INTO [dbo].[T_partition]
([Date]
,[Result])
VALUES
(DATEADD(MONTH,2, GETDATE()),'胜')
INSERT INTO [dbo].[T_partition]
([Date]
,[Result])
VALUES
(DATEADD(MONTH,2, GETDATE()),'负')
INSERT INTO [dbo].[T_partition]
([Date]
,[Result])
VALUES
(DATEADD(MONTH,1, GETDATE()),'胜')
INSERT INTO [dbo].[T_partition]
([Date]
,[Result])
VALUES
(DATEADD(MONTH,1, GETDATE()),'负')
WITH xx AS(
SELECT Date,Result
FROM dbo.T_partition
)
SELECT t.* FROM xx
PIVOT(COUNT(result) FOR RESULT IN ([胜],[负])) AS t
ORDER BY t.Date