快捷搜索:

SQL Server 2008中的Pivot和UnPivot

本日给新成员解说PIVOT 和 UNPIVOT示例,顺便收拾了一下其用法。这是自SQL Server 2005起供给的新功能。

官方示例:http://msdn.microsoft.com/zh-cn/library/ms177410%28v=sql.105%29.aspx

首先看PIVOT示例:

基础表数据:

IF NOT OBJECT_ID('tb_Income') IS NULL

DROP TABLE [tb_Income]

/奸淫奸淫 Object:Table [dbo].[tb_Income]Script Date: 2012/4/5 8:19:21 奸淫奸淫/

CREATE TABLE [dbo].[tb_Income](

----[PKID] int primary key identity(101,1),

[PName] [Nvarchar](20) NOT NULL,

[CYear] Smallint NOT NULL,

[CMonth] TinyInt NOT NULL,

[CMoney] Decimal (10,2) Not Null

)

GO

INSERT [dbo].[tb_Income]

SELECT '胡一刀',2011,2,5600

union ALL SELECT '胡一刀',2011,1,5678

union ALL SELECT '胡一刀',2011,3,6798

union ALL SELECT '胡一刀',2011,4,7800

union ALL SELECT '胡一刀',2011,5,8899

union ALL SELECT '胡一刀',2011,8,8877

union ALL SELECT '胡一刀',2011,6,7788

union ALL SELECT '胡一刀',2011,7,6798

union ALL SELECT '胡一刀',2011,10,10000

union ALL SELECT '胡一刀',2011,9,12021

union ALL SELECT '胡一刀',2011,11,8799

union ALL SELECT '胡一刀',2011,12,10002

union ALL SELECT '苗人凤',2011,1,3455

union ALL SELECT '苗人凤',2011,2,4567

union ALL SELECT '苗人凤',2011,3,5676

union ALL SELECT '苗人凤',2011,4,5600

union ALL SELECT '苗人凤',2011,5,6788

union ALL SELECT '苗人凤',2011,6,5679

union ALL SELECT '苗人凤',2011,7,6785

union ALL SELECT '苗人凤',2011,8,7896

union ALL SELECT '苗人凤',2011,9,7890

union ALL SELECT '苗人凤',2011,10,7799

union ALL SELECT '苗人凤',2011,11,9988

union ALL SELECT '郑希来',2011,2,5600

union ALL SELECT '郑希来',2011,3,2345

union ALL SELECT '郑希来',2011,5,12000

union ALL SELECT '郑希来',2011,4,23456

union ALL SELECT '郑希来',2011,6,4567

union ALL SELECT '郑希来',2011,7,6789

union ALL SELECT '郑希来',2011,8,9998

union ALL SELECT '郑希来',2011,9,34567

union ALL SELECT '郑希来',2011,12,5609

GO

测试结果如下:

SELECT * FROM tb_Income

GO

/*

PNameCYearCMonthCMoney

胡一刀201125600.00

胡一刀201115678.00

胡一刀201136798.00

胡一刀201147800.00

胡一刀201158899.00

胡一刀201188877.00

胡一刀201167788.00

胡一刀201176798.00

胡一刀20111010000.00

胡一刀2011912021.00

胡一刀2011118799.00

胡一刀20111210002.00

苗人凤201113455.00

苗人凤201124567.00

苗人凤201135676.00

苗人凤201145600.00

苗人凤201156788.00

苗人凤201165679.00

苗人凤201176785.00

苗人凤201187896.00

苗人凤201197890.00

苗人凤2011107799.00

苗人凤2011119988.00

郑希来201125600.00

郑希来201132345.00

郑希来2011512000.00

郑希来2011423456.00

郑希来201164567.00

郑希来201176789.00

郑希来201189998.00

郑希来2011934567.00

郑希来2011125609.00

*/

现在必要统计2011年的小我总人为,应用Group by 即可:

SELECT PName,sum(Cmoney) as YearMoney from tb_Income

GROUP BY PName

ORDER BY sum(Cmoney) desc

/*

PNameYearMoney

郑希来104931.00

胡一刀99060.00

苗人凤72123.00

*/

现在我们来进行行列转换:

SELECT CYear,胡一刀,苗人凤,郑希来 FROM tb_Income

PIVOT(sum(CMoney)

FOR PName IN

(胡一刀,苗人凤,郑希来)) t

/*

CYear胡一刀苗人凤郑希来

20115678.003455.00NULL

20115600.004567.005600.00

20116798.005676.002345.00

20117800.005600.0023456.00

20118899.006788.0012000.00

20117788.005679.004567.00

20116798.006785.006789.00

20118877.007896.009998.00

201112021.007890.0034567.00

201110000.007799.00NULL

20118799.009988.00NULL

201110002.00NULL5609.00

*/

留意行列已经转换,再汇总,关键是去除滋扰列,从新构建新数据集X:

SELECT 胡一刀,苗人凤,郑希来 FROM

(SELECT PName,CMoney FROM tb_Income) X

PIVOT(sum(CMoney)

FOR PName IN

(胡一刀,苗人凤,郑希来)) t

/*

胡一刀苗人凤郑希来

99060.0072123.00104931.00

*/

UNPIVOT的示例更简单一些:

天生基础数据:

IF NOT OBJECT_ID('tb_Tel') IS NULL

DROP TABLE [tb_Tel]

CREATE TABLE [dbo].[tb_Tel](

----[PKID] int primary key identity(101,1),

[PName] [Nvarchar](20) NOT NULL,

[Mobile1] [Nvarchar](20) NOT NULL,

[Mobile2] [Nvarchar](20) NOT NULL,

[Mobile3] [Nvarchar](20) Not Null

)

GO

INSERT [dbo].[tb_Tel]

SELECT '胡一刀','13067894562','13567889667','16767894562'

union ALL SELECT '苗人凤','1507894562','15267889667','15367894562'

union ALL SELECT '郑希来','18067894562','18567889667','18767894562'

GO

结果:

SELECT * FROM tb_Tel

/*

PNameMobile1Mobile2Mobile3

胡一刀130678945621356788966716767894562

苗人凤15078945621526788966715367894562

郑希来180678945621856788966718767894562

*/

行列转换:

SELECT PName,电话类型,电话号码

FROM tb_Tel

UNPIVOT(电话类型 FOR 电话号码 IN (Mobile1,Mobile2,Mobile3) ) p

/*

PName电话类型电话号码

胡一刀13067894562Mobile1

胡一刀13567889667Mobile2

胡一刀16767894562Mobile3

苗人凤1507894562Mobile1

苗人凤15267889667Mobile2

苗人凤15367894562Mobile3

郑希来18067894562Mobile1

郑希来18567889667Mobile2

郑希来18767894562Mobile3

*/

您可能还会对下面的文章感兴趣: