請指教怎樣用SQL陳述句把Atable變成Btable?
各位大神看起來這個問題似乎有點太簡單，但是還請不吝賜教，不勝感激
Table A
Team Cost Type Cost
A Business 20
B Capital 30
C External 28
D Business 35
A Capital 25
B Business 29
C Business 39
D External 44
Table B
Team Business Capital External
A 20 25 0
B 29 30 0
C 39 0 28
D 35 0 44
uj5u.com熱心網友回復：
一種方法：select Team,
max(case CostType when 'Business' then cost else 0 end) as 'Business',
max(case CostType when 'Capital' then cost else 0 end) as 'Capital',
max(case CostType when 'External' then cost else 0 end) as 'External'
from TableA
group by Team;
其實有專門處理這種問題的方法，有興趣你可以進一步了解 PIVOT
select Team,ISNULL([Business],0) as 'Business',ISNULL([Capital],0) as 'Capital',ISNULL([External],0) as 'External' from TableA a
PIVOT
(
max(a.cost) for a.CostType in([Business],[Capital],[External])
)b;
uj5u.com熱心網友回復：
如果Cost Type固定就那幾種，可以用靜態寫法，如果不固定可以用動態的https://blog.csdn.net/sinat_28984567/article/details/79865046
--測驗資料
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Team] nvarchar(21),[CostType] nvarchar(28),[Cost] int)
Insert #T
select N'A',N'Business',20 union all
select N'B',N'Capital',30 union all
select N'C',N'External',28 union all
select N'D',N'Business',35 union all
select N'A',N'Capital',25 union all
select N'B',N'Business',29 union all
select N'C',N'Business',39 union all
select N'D',N'External',44
Go
--測驗資料結束
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select [Team]'
SELECT @sql = @sql + ',max(case [CostType] when ''' + [CostType]
+ ''' then [Cost] else 0 end)[' + [CostType] + ']'
FROM ( SELECT DISTINCT
[CostType]
FROM #T
) a
SET @sql = @sql
+ ' from #T group by [Team]'
EXEC(@sql)
uj5u.com熱心網友回復：
謝謝樓上兩位大神
uj5u.com熱心網友回復：
用case when 傳統法。用T-SQL 中的pivot行轉列也能實作 。
這個很簡單
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/53893.html
標籤：疑難問題