T-SQL Two Dimension Pivot Table

將原始資料 

select BRAND='ASUS', QTY=1, AMT=200

union all

select BRAND='BENQ', QTY=1, AMT=200

union all

select BRAND='ASUS', QTY=1, AMT=200

union all

select BRAND='BENQ', QTY=1, AMT=200

union all

select BRAND='MSI', QTY=1, AMT=200

依各品牌匯總數量、金額，做出以下的統計表

解決方法 1 - Group By 加條件式 

select ASUS_QTY = SUM(IIF(Src.BRAND='ASUS', Src.QTY, 0))

, ASUS_AMT = SUM(IIF(Src.BRAND='ASUS', Src.AMT, 0))

, BENQ_QTY = SUM(IIF(Src.BRAND='BENQ', Src.QTY, 0))

, BENQ_AMT = SUM(IIF(Src.BRAND='BENQ', Src.AMT, 0))

, MSI_QTY = SUM(IIF(Src.BRAND='MSI', Src.QTY, 0))

, MSI_AMT = SUM(IIF(Src.BRAND='MSI', Src.AMT, 0))

from(

select BRAND='ASUS', QTY=1, AMT=200

union all

select BRAND='BENQ', QTY=1, AMT=200

union all

select BRAND='ASUS', QTY=1, AMT=200

union all

select BRAND='BENQ', QTY=1, AMT=200

union all

select BRAND='MSI', QTY=1, AMT=200

) Src

解決方法 2 - Pivot Table 

做多維度 Pivot Table 時，會排除相同的資料，導致第二維度彙總有遺漏資料狀況。 變通作法，先在每筆資料賦予唯一的流水號，即可解決。 

select ASUS_QTY = SUM(ASUS_QTY)

, ASUS_AMT = SUM(ASUS_AMT)

, BENQ_QTY = SUM(BENQ_QTY)

, BENQ_AMT = SUM(BENQ_AMT)

, MSI_QTY = SUM(MSI_QTY)

, MSI_AMT = SUM(MSI_AMT)

from (

select RNO=row_number() over (order by BRAND)

, BRAND1 = Src.BRAND+'_QTY'

, BRAND2 = Src.BRAND+'_AMT'

, Src.*

from(

select BRAND='ASUS', QTY=1, AMT=200

union all

select BRAND='BENQ', QTY=1, AMT=200

union all

select BRAND='ASUS', QTY=1, AMT=200

union all

select BRAND='BENQ', QTY=1, AMT=200

union all

select BRAND='MSI', QTY=1, AMT=200

) Src

) T

PIVOT

(

SUM (QTY)

FOR BRAND1 IN ([ASUS_QTY],[BENQ_QTY],[MSI_QTY])

) AS pvt1

PIVOT

(

SUM (AMT)

FOR BRAND2 IN ([ASUS_AMT],[BENQ_AMT],[MSI_AMT])

) AS pvt2