create table t
(
name varchar(20),
scount int,
type1 varchar(20),
type2 varchar(20)
)insert into t
SELECT 'us1',4,'pc','sa' UNION ALL
SELECT 'us1',3,'pc','sa' UNION ALL
SELECT 'us2',2,'dc','ta' UNION ALL
SELECT 'us2',6,'ec','ta' UNION ALL
SELECT 'us2',1,'dc','ta' UNION ALL
SELECT 'us3',5,'ec','sa' UNION ALL
SELECT 'us3',3,'ec','ta' UNION ALL
SELECT 'us4',1,'pc','sa' DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT name'
SELECT @sql = @sql + ',['+type+'] = ISNULL(SUM(CASE WHEN type = '''+type+''' THEN scount
END),0)'
FROM
( SELECT name,scount,type = type1 FROM t
UNION ALL
SELECT name,scount,type = type2 FROM t
) A GROUP BY type
EXEC(@sql + ' FROM ( SELECT name,scount,type = type1 FROM t
UNION ALL
SELECT name,scount,type = type2 FROM t
) A GROUP BY name order by name')drop table t name dc ec pc sa ta
-------------------- ----------- ----------- ----------- ----------- -----------
us1 0 0 7 7 0
us2 3 6 0 0 9
us3 0 8 0 5 3
us4 0 0 1 1 0
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
us1 7 0 0 7 0
create table dw
(
name varchar(20),
scount int,
type1 varchar(20),
type2 varchar(20)
)insert into dw
SELECT 'us1',4,'pc','sa' UNION ALL
SELECT 'us1',3,'pc','sa' UNION ALL
SELECT 'us2',2,'dc','ta' UNION ALL
SELECT 'us2',6,'ec','ta' UNION ALL
SELECT 'us2',1,'dc','ta' UNION ALL
SELECT 'us3',5,'ec','sa' UNION ALL
SELECT 'us3',3,'ec','ta' UNION ALL
SELECT 'us4',1,'pc','sa'
declare @sql varchar(1000)
set @sql='select name'
select @sql=@sql+',['+ type1 +']=sum(case type1 when '''+ type1 +''' then scount else 0 end)' from (select distinct type1 from dw)a
select @sql=@sql+',['+ type2 +']=sum(case type2 when '''+ type2 +''' then scount else 0 end)' from (select distinct type2 from dw)a
set @sql=@sql+' from dw group by name'
exec(@sql)drop table dw
create table dw
(
name varchar(20),
scount int,
type1 varchar(20),
type2 varchar(20)
)insert into dw
SELECT 'us1',4,'pc','sa' UNION ALL
SELECT 'us1',3,'pc','sa' UNION ALL
SELECT 'us2',2,'dc','ta' UNION ALL
SELECT 'us2',6,'ec','ta' UNION ALL
SELECT 'us2',1,'dc','ta' UNION ALL
SELECT 'us3',5,'ec','sa' UNION ALL
SELECT 'us3',3,'ec','ta' UNION ALL
SELECT 'us4',1,'pc','sa' select name,
[pc]=sum(case when type1='pc' then scount else 0 end),
[dc]=sum(case when type1='dc' then scount else 0 end),
[ec]=sum(case when type1='ec' then scount else 0 end),
[sa]=sum(case when type2='sa' then scount else 0 end),
[ta]=sum(case when type2='ta' then scount else 0 end)
from dw group by name name pc dc ec sa ta
-------------------- ----------- ----------- ----------- ----------- -----------
us1 7 0 0 7 0
us2 0 3 6 0 9
us3 0 0 8 5 3
us4 1 0 0 1 0
go
create table dw
(
name varchar(20),
scount int,
type1 varchar(20),
type2 varchar(20)
)insert into dw
SELECT 'us1',4,'pc','sa' UNION ALL
SELECT 'us1',3,'pc','sa' UNION ALL
SELECT 'us2',2,'dc','ta' UNION ALL
SELECT 'us2',6,'ec','ta' UNION ALL
SELECT 'us2',1,'dc','ta' UNION ALL
SELECT 'us3',5,'ec','sa' UNION ALL
SELECT 'us3',3,'ec','ta' UNION ALL
SELECT 'us4',1,'pc','sa'
go
declare @s varchar(8000),@s2 varchar(8000)--字符长时用两个变量
select @s='',@s2=''
select @s=@s+','+quotename(type1)+'=sum(case when [type1]='+quotename(type1,'''')+' then scount else 0 end)'
from dw group by type1select @s2=@s2+','+quotename(type2)+'=sum(case when [type2]='+quotename(type2,'''')+' then scount else 0 end)'
from dw group by type2
exec ('select name'+@s+@s2+' from dw group by name ')
--以下是动态生成的语句
select
name,
[dc]=sum(case when [type1]='dc' then scount else 0 end),
[ec]=sum(case when [type1]='ec' then scount else 0 end),
[pc]=sum(case when [type1]='pc' then scount else 0 end),
[sa]=sum(case when [type2]='sa' then scount else 0 end),
[ta]=sum(case when [type2]='ta' then scount else 0 end)
from
dw
group by name
/*
name dc ec pc sa ta
-------------------- ----------- ----------- ----------- ----------- -----------
us1 0 0 7 7 0
us2 3 6 0 0 9
us3 0 8 0 5 3
us4 0 0 1 1 0
*/