--行列轉換問題- ----建立測試環境 drop table tableacreate table tablea (name varchar(50), a varchar(50), b int )insert into TableA select 'lick','an',3 union all select 'lick','an',3 union all select 'lick3','ac',4 union all select 'lick4','an',3 union all select 'lick5','an',3 union all select 'lick','aan',2 union all select 'lick6','ab',5 union all select 'lick7','dn',3 union all select 'lick8','an',6 select * from tablea name a b lick an 3 lick an 3 lick3 ac 4 lick4 an 3 lick5 an 3 lick aan 2 lick6 ab 5 lick7 dn 3 lick8 an 6 ------解決方案 declare @a varchar(1000) set @a='select name ' select @a=@a+',sum(case when a='''+a+''' then b end)'+' as '+ a ----這裡sum改為min或max結果一樣 from (select distinct a from TableA)T1 set @a=@a+' from TableA group by name' --print (@a) 測試 exec(@a) --測試結果結果 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
----建立測試環境
drop table tableacreate table tablea
(name varchar(50),
a varchar(50),
b int
)insert into TableA
select 'lick','an',3 union all
select 'lick','an',3 union all
select 'lick3','ac',4 union all
select 'lick4','an',3 union all
select 'lick5','an',3 union all
select 'lick','aan',2 union all
select 'lick6','ab',5 union all
select 'lick7','dn',3 union all
select 'lick8','an',6
select * from tablea
name a b
lick an 3
lick an 3
lick3 ac 4
lick4 an 3
lick5 an 3
lick aan 2
lick6 ab 5
lick7 dn 3
lick8 an 6
------解決方案
declare @a varchar(1000)
set @a='select name '
select @a=@a+',sum(case when a='''+a+''' then b end)'+' as '+ a ----這裡sum改為min或max結果一樣
from (select distinct a from TableA)T1
set @a=@a+' from TableA group by name'
--print (@a) 測試
exec(@a)
--測試結果結果
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