SQL Pivot查询透视值

iItemKey    Qty FreeQty Unit  TaxCatKey TaxVal
    7        1    1      1        1      4.00
    7        1    1      1        1      1.00

I need output as

我需要输出为

iItemKey    Qty FreeQty Unit  TaxCatKey   VAT  A.VAt
    7        1    1      1        1       4.00   1.00 

Here is my query. But i get VAT and A.Vat values as Null

这是我的查询。但我得到增值税和A.Vat值为Null

WITH T
     AS (SELECT     T_ItemRequestSub.iItemKey, T_ItemRequestSub.Qty, T_ItemRequestSub.FreeQty, T_ItemRequestSub.Unit, T_ItemRequestSub.TaxType, 
                   M_Mt_TaxCategorySub.iTaxCatKey ,  M_Mt_TaxCategorySub.iTaxVal
FROM         T_ItemRequestSub INNER JOIN
                      T_ItemRequest ON T_ItemRequestSub.iReqKey = T_ItemRequest.iKey INNER JOIN
                      M_Mt_TaxCategory ON T_ItemRequestSub.TaxType = M_Mt_TaxCategory.iKey INNER JOIN
                      M_Mt_TaxCategorySub ON M_Mt_TaxCategory.iKey = M_Mt_TaxCategorySub.iTaxCatKey where T_ItemRequestSub.iKey = 2)
SELECT *
FROM   T PIVOT ( sum (iTaxVal) FOR TaxType IN ( 
       [Vat],
       [A.Vat]
      ) ) AS pvt

Please Help

请帮忙

1 个解决方案

#1

The only distinct value is TaxVal so I suspect you are trying to do following:

唯一不同的值是TaxVal，所以我怀疑你正在尝试做以下事情：

WITH Src AS --Your source table
(
    SELECT * FROM (VALUES
    (7, 1, 1, 1, 1, 4.00),
    (7, 1, 1, 1, 1, 1.00)
    )T(iItemKey, Qty, FreeQty, Unit, TaxCatKey, TaxVal)
)
SELECT * FROM
(
    SELECT iItemKey, Qty, FreeQty, Unit, TaxCatKey, TaxVal, CASE WHEN TaxVal = 1.00 THEN 'VAT' ELSE 'A.VAT' END Col
    FROM Src
) T
PIVOT (MAX(TaxVal) FOR Col IN ([VAT], [A.VAT])) P