create table t(Name varchar(10),Value int,Date varchar(10),TZTZ char(1))
insert into t select 'A',10 ,'200101','Y'
insert into t select 'B',20 ,'200101','Y'
insert into t select 'A',45 ,'200102','Y'
insert into t select 'B',55 ,'200102','Y'
insert into t select 'A',110,'200101','N'
insert into t select 'B',120,'200101','N'
insert into t select 'A',145,'200102','N'
insert into t select 'B',155,'200102','N'declare @s varchar(8000)
set @s='select Name'
select @s=@s+',['+rtrim(Date)+'('+TZTZ+')]=sum(case when Date='''+Date+''' and TZTZ='''+TZTZ+''' then Value end)'
from t group by Date,TZTZ order by Date,TZTZ Desc
set @s=@s+' from t group by Name'
exec(@s)/*
Name 200101(Y) 200101(N) 200102(Y) 200102(N)
---------- ----------- ----------- ----------- -----------
A 10 110 45 145
B 20 120 55 155
*/drop table t
insert into t select 'A',10 ,'200101','Y'
insert into t select 'B',20 ,'200101','Y'
insert into t select 'A',45 ,'200102','Y'
insert into t select 'B',55 ,'200102','Y'
insert into t select 'A',110,'200101','N'
insert into t select 'B',120,'200101','N'
insert into t select 'A',145,'200102','N'
insert into t select 'B',155,'200102','N'declare @s varchar(8000)
set @s='select Name'
select @s=@s+',['+rtrim(Date)+'('+TZTZ+')]=sum(case when Date='''+Date+''' and TZTZ='''+TZTZ+''' then Value end)'
from t group by Date,TZTZ order by Date,TZTZ Desc
set @s=@s+' from t group by Name'
exec(@s)/*
Name 200101(Y) 200101(N) 200102(Y) 200102(N)
---------- ----------- ----------- ----------- -----------
A 10 110 45 145
B 20 120 55 155
*/drop table t
insert TB_TEST
select 'A',10,'200101','Y' union all
select 'B',20,'200101','Y' union all
select 'A',45,'200102','Y' union all
select 'B',55,'200102','Y' union all
select 'A',110,'200101','N' union all
select 'B',120,'200101','N' union all
select 'A',145,'200102','N' union all
select 'B',155,'200102','N'go
declare @s varchar(8000)set @s = ''select @s = @s +',['+date+'('+TZTZ+')]= sum(case when date='''+date+
''' and tztz='''+tztz+''' then Value end) '
from (select distinct date,TZTZ from TB_TEST) a
--set @s1 = stuff(@s1,1,1,'')
exec ('select name '+@s +' from tb_test group by name')
print 'select name '+@s
drop table TB_TEST
/*name 200101(N) 200101(Y) 200102(N) 200102(Y)
---- ----------- ----------- ----------- -----------
A 110 10 145 45
B 120 20 155 55
*/