SQL SERVER PIVOT 行列转换

来源：互联网发布：手机挂机赚钱软件编辑：程序博客网时间：2024/04/30 13:55

原理：利用#Temp临时表 + sp_executesql 文本sql的方式。解决完全用文本SQL实现所有SQL的问题，那样代码不好检查错误和编写。

最近项目中用到Pivot 对表进行转置，遇到一些问题，主要是Pivot 转置的时候没有办法动态产生转置列名,而作视图的时候又很需要动态的产生这些列，百度上似乎也没有找的很满意的答案，在google上搜到一老外的解决方案，现在自己总结了一下，希望给用的上的朋友一些帮助。

1.创建表脚本

if exists (select 1

from sysobjects

where id = object_id('Insurances')

and type = 'U')

drop table Insurances

/*==============================================================*/

/* Table: Insurances */

/*==============================================================*/

create table Insurances (

RefID uniqueidentifier not null,

HRMS nvarchar(20) null,

Name nvarchar(20) null,

InsuranceMoney money null,

InsuranceName nvarchar(100) not null,

constraint PK_INSURANCES primary key (RefID)

)

2.测试数据脚本

insert into Insurances values (newid(),1,'张三',200,'养老保险')

insert into Insurances values (newid(),1,'张三',300,'医疗保险')

insert into Insurances values (newid(),2,'李四',250,'养老保险')

insert into Insurances values (newid(),2,'李四',350,'医疗保险')

insert into Insurances values (newid(),3,'王二',150,'养老保险')

insert into Insurances values (newid(),3,'王二',300,'医疗保险')

3.查询表数据

select HRMS,Name,InsuranceMoney,InsuranceName From Insurances

HRMS Name InsuranceMoney InsuranceName

-------------------- -------------------- --------------------- ----------

1 张三 200.00 养老保险

2 李四 350.00 医疗保险

2 李四 250.00 养老保险

1 张三 300.00 医疗保险

3 王二 300.00 医疗保险

3 王二 150.00 养老保险

4.转置表数据

select * from

(

select HRMS,Name,InsuranceMoney,InsuranceName from Insurances

) p

Pivot (

sum(InsuranceMoney)

FOR InsuranceName IN

( [医疗保险], [养老保险]))

as pvt

HRMS Name 医疗保险养老保险

-------------------- -------------------- --------------------- ---------------------

2 李四 350.00 250.00

3 王二 300.00 150.00

1 张三 300.00 200.00

5.偶的问题

images/pivot20080719002.gif

这个语句中医疗保险、养老保险是SQL语句中写死的，而且Sql2005中这个代码没有办法使用动态的查询结果集

5.存储过程解决问题

所以如果要动态的完成个脚本，可以先拼出SQL 然后通过exec sp_executesql 执行

实现存储过程

create procedure InsurancePivot

Begin

DECLARE @ColumnNames VARCHAR(3000)

SET @ColumnNames=''

SELECT

@ColumnNames = @ColumnNames + '[' + InsuranceName + '],'

FROM

(

SELECT DISTINCT InsuranceName FROM Insurances

) t

SET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1)

DECLARE @selectSQL NVARCHAR(3000)

SET @selectSQL=

'SELECT HRMS,Name,{0} FROM

(

SELECT HRMS,Name,InsuranceMoney,InsuranceName FROM Insurances

) p

Pivot( Max(InsuranceMoney) For InsuranceName in ({0})) AS pvt

ORDER BY HRMS'

SET @selectSQL= REPLACE(@selectSQL,'{0}',@ColumnNames)

exec sp_executesql @selectSQL

end

测试存储过程：

exec InsurancePivot

HRMS Name 养老保险医疗保险

-------------------- -------------------- --------------------- ---------------------

1 张三 200.00 300.00

2 李四 250.00 350.00

3 王二 150.00 300.00

==================================================== 另外一种行专列 ====================

declare @t table ([name] varchar(20),score int,testNo int)
insert into @t
select 'a',90, 1 union all
select 'a',59, 2 union all
select 'a',39, 3 union all
select 'b',75, 1

select [name]+
(select ','+cast(score as varchar(20)) from @t as X where X.[name]=Y.[name] order by testNo
for xml path('')) as test from @t as Y
group by [name]