SQL for 100 columns, 2 Values For Each Column

Question

I came up with below script:-

SELECT *
FROM (
    SELECT [COST UNIT], [GL ACCOUNT]+ ' ' + [GL] AS GLDetails, [ITEM CODE], [DESCRIPTION], PACKAGING, [U/MEA.], [STD COST PRICE], 
    MAX([STOCK QTY]) AS STOCKQTY, MAX([CLOSING BALANCE]) AS CLOSINGBALANCE FROM 
    (
            SELECT AB.StartDate AS [DATE], AB.FreeTextField_04 AS [COST UNIT], IT.GLAccountDistribution AS [GL ACCOUNT], GL.Description AS [GL], ST.ItemCode AS [ITEM CODE] , 
            ST.Description AS [DESCRIPTION], IT.UserField_03 AS PACKAGING, ST.StockOnHandUOM AS [U/MEA.], IT.CostPriceStandard AS [STD COST PRICE],
            ST.PhyStkTakeQty AS [STOCK QTY], CAST(ROUND((ST.PhyStkTakeQty * IT.CostPriceStandard ),2) AS DECIMAL(12,2)) AS [CLOSING BALANCE]
            FROM [001].DBO.CS738281StkTake ST INNER JOIN [001].DBO.Absences AB ON ST.ReqID = AB.ID 
            INNER JOIN [200].DBO.Items IT ON ST.ItemCode = IT.ItemCode
            INNER JOIN [200].DBO.GRV_GLAccounts GL ON IT.GLAccountDistribution = GL.GLAccount
            WHERE AB.StartDate = '2017-08-31 0:00:00.000'
            AND IT.GLAccountDistribution IN (12010010,12010020,12010030,12010040,12010041,12010042,12010043,12010050,12010060,12010080,12030010)
    ) tmp
    GROUP BY [COST UNIT], [GL ACCOUNT], [GL], [ITEM CODE], DESCRIPTION, PACKAGING, [U/MEA.], [STD COST PRICE], [STOCK QTY]
) as s
ORDER BY [COST UNIT], GLDetails, [ITEM CODE]

The result will return something like this:-

My objective is to show the pivot table for 2 column, [STOCK QTY] and CLOSINGBALANCE, something like this:-

I am not sure of how to do unpivot, or if can use 2 pivots?

I have created below script for the pivot but I dont think it can work:-

PIVOT
(
    MAX(STOCKQTY)
    FOR [COST UNIT] IN ([1CTY],[AMCP],[AMPO],[AMW],[ARWG],[AVK],[BCAV],[BKTS],[BREM],[BSD],[BSP],[BSS],[BTS],[BU2],[CM],[CONN],[CTM],[CTP],[CURV],[DIP],[DPC],
[DSPD],[EMPC],[EQP],[GBK],[GEM],[GSK],[GSTP],[HLGT],[IOI],[IOIC],[IPAE],[IPAK],[IPGD],[IPGT],[IPGU],[IPKC],[IPMH],[IPPR],[ITM],[JCS],[JY33],[KDSP],[KDVM],[KLCC],
[KLFC],[KMT],[KP],[KPAR],[KSEL],[KUBU],[LM2],[MAJU],[MALU],[MCA],[MINE],[MLWT],[MV],[NOTT],[PI],[PJO],[PNBB],[PNEG],[PNGP],[PNQM],[PTJ],[QUIL],[SAAE],[SACC],
[SAGI],[SAS7],[SAS9],[SEDG],[SGB],[SGBS],[SGLG],[SGWG],[SLY],[SP],[SPT],[SPY],[STAR],[SUM],[SUT3],[SWPT],[TAM],[TKRG],[TP],[TRCM],[TRTY],[TTD2],[TTDI],[UOAB],
[WELD],[WLR],[WMAA])
)AS p1

Thank you in advance for the replies :)

please make it easier, provide simple sample data, simple expected output and read [this](https://stackoverflow.com/help/mcve) — Hatik, Oct 30 '17 at 07:21

score 0 · Answer 1 · edited Nov 01 '17 at 09:42

WITH COST_VALUE AS
(
SELECT ST.Description,
 AB.FreeTextField_04 AS [COST UNIT],
            ST.PhyStkTakeQty AS [STOCK QTY], CAST(ROUND((ST.PhyStkTakeQty * IT.CostPriceStandard ),2) AS DECIMAL(12,2)) AS [CLOSING BALANCE]
            FROM DBO.CS738281StkTake ST INNER JOIN .DBO.Absences AB ON ST.ab_id = AB.ID
            INNER JOIN DBO.Items IT ON ST.ItemCode = IT.ItemCode
            INNER JOIN DBO.GRV_GLAccounts GL ON IT.GLAccountDistribution = GL.GLAccount
            AND IT.GLAccountDistribution IN (12010010,12010020,12010030,12010040,12010041,12010042,12010043,12010050,12010060,12010080,12030010)
)
SELECT S.[COST UNIT], S.GLDetails, S.[ITEM CODE], S.[DESCRIPTION], S.PACKAGING, S.[U/MEA.], S.[STD COST PRICE]
,CTY.[STOCK QTY] AS [1CTY STOCK QTY],CTY.[CLOSING BALANCE] AS [1CTY CLOSING BALANCE]
,AMP.[STOCK QTY] AS [AMCP STOCK QTY],AMP.[CLOSING BALANCE] AS [AMCP CLOSING BALANCE]
,AMO.[STOCK QTY] AS [AMPO STOCK QTY],AMO.[CLOSING BALANCE] AS [AMPO CLOSING BALANCE]
FROM (
    SELECT [COST UNIT], cast([GL ACCOUNT] as varchar(100))+ ' ' + [GL] AS GLDetails, [ITEM CODE], [DESCRIPTION], PACKAGING, [U/MEA.], [STD COST PRICE],
    MAX([STOCK QTY]) AS STOCKQTY, MAX([CLOSING BALANCE]) AS CLOSINGBALANCE FROM
    (
            SELECT AB.StartDate AS [DATE], AB.FreeTextField_04 AS [COST UNIT], IT.GLAccountDistribution AS [GL ACCOUNT], GL.Description AS [GL], ST.ItemCode AS [ITEM CODE] ,
            ST.Description AS [DESCRIPTION], IT.UserField_03 AS PACKAGING, ST.StockOnHandUOM AS [U/MEA.], IT.CostPriceStandard AS [STD COST PRICE],
            ST.PhyStkTakeQty AS [STOCK QTY], CAST(ROUND((ST.PhyStkTakeQty * IT.CostPriceStandard ),2) AS DECIMAL(12,2)) AS [CLOSING BALANCE]
            FROM DBO.CS738281StkTake ST INNER JOIN .DBO.Absences AB ON ST.ab_id = AB.ID
            INNER JOIN DBO.Items IT ON ST.ItemCode = IT.ItemCode
            INNER JOIN DBO.GRV_GLAccounts GL ON IT.GLAccountDistribution = GL.GLAccount
            AND IT.GLAccountDistribution IN (12010010,12010020,12010030,12010040,12010041,12010042,12010043,12010050,12010060,12010080,12030010)
    ) tmp
    GROUP BY [COST UNIT], [GL ACCOUNT], [GL], [ITEM CODE], DESCRIPTION, PACKAGING, [U/MEA.], [STD COST PRICE], [STOCK QTY]
) as S
LEFT JOIN COST_VALUE CTY ON CTY.Description = s.DESCRIPTION and CTY.[COST UNIT] = '1CTY'
LEFT JOIN COST_VALUE AMP ON AMP.Description = s.DESCRIPTION and CTY.[COST UNIT] = 'AMCP'
LEFT JOIN COST_VALUE AMO ON AMO.Description = s.DESCRIPTION and CTY.[COST UNIT] = 'AMPO'
ORDER BY S.[COST UNIT], S.GLDetails, S.[ITEM CODE]

SQL for 100 columns, 2 Values For Each Column

1 Answers1