数据如下
ProjectID Lead name
PD0001 0.1 a
PD0001 0.2 b
PD0001 1.1 c
PD0001 2.1 d PD0002 2.1 a
PD0002 2.2 b
PD0002 2.9 c
PD0002 2.1 d PD0003 3.1 a
PD0003 3.2 b
PD0003 3.1 c
PD0001 3.1 d 通过sql语句后显示如下
ProjectID a b c d
PD0001 0.1 0.2 1.1 2.1
PD0002 2.1 2.2 2.9 2.1
PD0003 3.1 3.2 3.1 3.1
ProjectID Lead name
PD0001 0.1 a
PD0001 0.2 b
PD0001 1.1 c
PD0001 2.1 d PD0002 2.1 a
PD0002 2.2 b
PD0002 2.9 c
PD0002 2.1 d PD0003 3.1 a
PD0003 3.2 b
PD0003 3.1 c
PD0001 3.1 d 通过sql语句后显示如下
ProjectID a b c d
PD0001 0.1 0.2 1.1 2.1
PD0002 2.1 2.2 2.9 2.1
PD0003 3.1 3.2 3.1 3.1
select ProjectID,
a=max(case when name='a' then lead else 0.0 end),
b=max(case when name='b' then lead else 0.0 end),
c=max(case when name='c' then lead else 0.0 end),
d=max(case when name='d' then lead else 0.0 end)
from tb
group by ProjectID
go
insert into table1 select
'PD0001',0.1,'a' union all select
'PD0001',0.2,'b' union all select
'PD0001',1.1,'c' union all select
'PD0001',2.1,'d' union all select
'PD0002',2.1,'a' union all select
'PD0002',2.2,'b' union all select
'PD0002',2.9,'c' union all select
'PD0002',2.1,'d' union all select
'PD0003',3.1,'a' union all select
'PD0003',3.2,'b' union all select
'PD0003',3.1,'c' union all select
'PD0001',3.1,'d'
declare @str as nvarchar(1000) select @str=ISNULL(@str,'select ProjectID ') + ',' + QUOTENAME(name)+ '=max(case name when '''+name+''' then Lead else 0 end)'
from (select distinct name from table1) tb
select @str=@str + ' from table1 group by ProjectID'exec(@str)
/*(12 行受影响)
ProjectID a b c d
---------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
PD0001 0.1 0.2 1.1 3.1
PD0002 2.1 2.2 2.9 2.1
PD0003 3.1 3.2 3.1 0.0(3 行受影响)*/
drop table table1
go
insert into table1 select
'PD0001',0.1,'a' union all select
'PD0001',0.2,'b' union all select
'PD0001',1.1,'c' union all select
'PD0001',2.1,'d' union all select
'PD0002',2.1,'a' union all select
'PD0002',2.2,'b' union all select
'PD0002',2.9,'c' union all select
'PD0002',2.1,'d' union all select
'PD0003',3.1,'a' union all select
'PD0003',3.2,'b' union all select
'PD0003',3.1,'c' union all select
'PD0003',3.1,'d'
declare @str as nvarchar(1000) select @str=ISNULL(@str,'select ProjectID ') + ',' + QUOTENAME(name)+ '=max(case name when '''+name+''' then Lead else 0 end)'
from (select distinct name from table1) tb
select @str=@str + ' from table1 group by ProjectID'exec(@str)
/*
ProjectID a b c d
---------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
PD0001 0.1 0.2 1.1 2.1
PD0002 2.1 2.2 2.9 2.1
PD0003 3.1 3.2 3.1 3.1(3 行受影响)
*/
drop table table1
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-22 22:52:41
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ProjectID] varchar(6),[Lead] numeric(2,1),[name] varchar(1))
insert [tb]
select 'PD0001',0.1,'a' union all
select 'PD0001',0.2,'b' union all
select 'PD0001',1.1,'c' union all
select 'PD0001',2.1,'d' union all
select 'PD0002',2.1,'a' union all
select 'PD0002',2.2,'b' union all
select 'PD0002',2.9,'c' union all
select 'PD0002',2.1,'d' union all
select 'PD0003',3.1,'a' union all
select 'PD0003',3.2,'b' union all
select 'PD0003',3.1,'c' union all
select 'PD0001',3.1,'d'
--------------开始查询--------------------------
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + name from tb group by name
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(Lead) for name in (' + @sql + ')) b')
----------------结果----------------------------
/*ProjectID a b c d
--------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
PD0001 0.1 0.2 1.1 3.1
PD0002 2.1 2.2 2.9 2.1
PD0003 3.1 3.2 3.1 NULL(3 行受影响)
*/