Edburg Edburg - 2 months ago 8
SQL Question

SQL Pivot Query for pivot values

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

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

Answer

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

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