表结构入下: A1 A2 test zz test2 kk te mm . . . . . . 很多条记录,而且A1和A2是关联的数据 下面要转成 A1 test test2 te . . . A2 zz kk mm . . . 的数据表现格式,因为这样的话,在报表中的显示就会行和列的对应显示,但是很郁闷的是,想半天没有结果,请各位大哥帮忙了,谢谢了,谢谢
declare @s nvarchar(4000)
set @s=''
select @s=@s+','
+'max(case when a= '''+a +''' then b end)' +' as ' +quotename(a)--max可改sum,min
from (select distinct a from tablea)T
set @s=stuff(@s,1,1,'')
set @s='select a,'+@s+' from tablea group by a'
exec (@s)
--測試結果結果
name aan ab ac an dn
lick 2 NULL NULL 6 NULL
lick3 NULL NULL 4 NULL NULL
lick4 NULL NULL NULL 3 NULL
lick5 NULL NULL NULL 3 NULL
lick6 NULL 5 NULL NULL NULL
lick7 NULL NULL NULL NULL 3
lick8 NULL NULL NULL 6 NULL
-------存儲過程----
CREATE proc ColTorow
as
set nocount on
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+a --quotoname(a)
+'=isnull(rtrim(max(case when a= ' ----max可改成sum
+ QUOTENAME(a,'''')
+' then b end)),'''')'---isnull(a,'')函數----在這樣修改可改成你想要的
---- +' then b end)),''AA'')'
from tablea group by a
exec('select name '+@s+' from tablea group by name')
GO
----調用
ColTorow
----------------結果
name aan ab ac an dn
lick 2 3
lick3 4
lick4 3
lick5 3
lick6 5
lick7 3
lick8 6
----刪除測試環境
drop table tablea
drop proc coltorow