sql server（PIVOT）行列转换_pivot as pvt-优快云博客

本文介绍如何利用SQL脚本、存储过程及动态查询实现保险数据的实时分析，包括创建表、插入测试数据、查询数据、数据转置以及通过存储过程动态完成数据分析任务。重点展示了SQL在数据处理和分析中的应用，以及如何通过存储过程提高代码的灵活性和复用性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

if exists(select name from sysobjects
          where name='Proc_InsurancePivot' and type = 'p')
   drop procedure Proc_InsurancePivot
GO
create procedure Proc_InsurancePivot
as
set nocount on
Begin
    DECLARE @ColumnNames VARCHAR(3000)
    SET @ColumnNames=''
    SELECT
       @ColumnNames = @ColumnNames + '[' + elementName + '],'
    FROM
       (
       SELECT DISTINCT elementName FROM CMS_FormType where IsShow='True'
       ) t
    SET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1)
    DECLARE @selectSQL NVARCHAR(3000)
    SET @selectSQL='SELECT * FROM(
        select a.FormValue,a.groupId,a.createon,b.elementName from dbo.CMS_FormValue a
inner join dbo.CMS_FormType b
on a.FormId=b.FormId
where b.IsShow=''True''
) p
     Pivot(Max(FormValue) For elementName in ({0})) AS pvt order by createon desc'
    SET @selectSQL= REPLACE(@selectSQL,'{0}',@ColumnNames)
    exec sp_executesql @selectSQL
end

exec Proc_InsurancePivot

select * from CMS_FormValue

SELECT * FROM(
   select a.FormValue,a.groupId,a.createon,b.elementName from dbo.CMS_FormValue a
inner join dbo.CMS_FormType b
on a.FormId=b.FormId
where b.IsShow='True'
) as p
     Pivot(max(FormValue) For elementName in ([Email],[留言内容],[爱好])) AS pvt order by createon desc