--行列轉換問題-
----建立測試環境
drop table tableadeclare @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
----建立測試環境
drop table tableadeclare @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
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货