T-SQL PIVOT 基础使用

前言

题外话，用行动来表示坚持与执着，相差还甚远，继续在路上。

摘要

PIVOT运算符用于在行列之间对数据进行旋转或透视转换，同时执行聚合运算。
官网文档：http://technet.microsoft.com/en-us/library/ms177410(v=SQL.105).aspx

基础用法

1. 先准备好数据

USE tempdb;
GO

IF OBJECT_ID('StudentScores','U') IS NOT NULL
   DROP TABLE [StudentScores];

CREATE TABLE [StudentScores]
(
 [UserName] NVARCHAR(20),
 [Subject]  NVARCHAR(30),
 [Score]    FLOAT
);
GO

INSERT INTO [StudentScores] SELECT 'Lily', N'语文',60
UNION ALL
SELECT 'Lily',N'数学',NULL  --此处添加NULL是为提醒在使用AVG()时不包括NULL
UNION ALL
SELECT 'Lily',N'英语',NUll
UNION ALL
SELECT 'Lily',N'物理',90
UNION ALL
SELECT 'Tom',N'语文',90
UNION ALL
SELECT 'Tom',N'数学',90
UNION ALL
SELECT 'Tom',N'英语',70
UNION ALL
SELECT 'Tom',N'物理',85
UNION ALL
SELECT 'Jerry',N'语文',90
UNION ALL
SELECT 'Jerry', N'数学',86
UNION ALL
SELECT 'Jerry', N'英语',90
UNION ALL
SELECT 'Jerry', N'物理', 89;
GO

View Code

现在的信息

2.先SELECT...CASE行转列看效果

SELECT UserName,
  MAX(CASE Subject WHEN N'语文' THEN Score ELSE 0 END) AS N'语文',
  MAX(CASE Subject WHEN N'数学' THEN Score ELSE 0 END) AS N'数学',
  MAX(CASE Subject WHEN N'英语' THEN Score ELSE 0 END) AS N'英语',
  MAX(CASE Subject WHEN N'物理' THEN Score ELSE 0 END) AS N'物理'
FROM dbo.StudentScores
GROUP BY UserName;
GO

3.使用PIVOT行转列

SELECT UserName,语文,数学,英语,物理
FROM (
 SELECT UserName, [Subject], Score FROM dbo.StudentScores
) AS S
PIVOT (MAX(Score) FOR [Subject] 
       IN (语文,数学,英语,物理)) AS P;
GO