if object_id('test') is not null drop table test select 'xm' as zd1, 'dd' as zd2, 5 as zd3, 1 as zd4 into test union select 'xm2', 'dd2', 3, 2 ------------------------------------------------- declare @s varchar(2000) set @s = 'select zd1' select @s = @s + ', isnull(min(case when zd2 = ''' + zd2 + ''' then cast(zd3 as varchar) + ''/'' + cast(zd4 as varchar) end), ''0/0'') as [' + zd2 + ']' from (select distinct zd2 from test) a set @s = @s + ' from test group by zd1' exec(@s) /* zd1 dd dd2 xm 5/1 0/0 xm2 0/0 3/2 */ ------------------------------------------------- drop table test
select 'xm' as zd1, 'dd' as zd2, 5 as zd3, 1 as zd4
into test
union select 'xm2', 'dd2', 3, 2
-------------------------------------------------
declare @s varchar(2000)
set @s = 'select zd1'
select @s = @s + ', isnull(min(case when zd2 = ''' + zd2
+ ''' then cast(zd3 as varchar) + ''/'' + cast(zd4 as varchar) end), ''0/0'') as [' + zd2 + ']'
from (select distinct zd2 from test) a
set @s = @s + ' from test group by zd1'
exec(@s)
/*
zd1 dd dd2
xm 5/1 0/0
xm2 0/0 3/2
*/
-------------------------------------------------
drop table test