Kazuya Marino Kazuya Marino - 3 months ago 14
SQL Question

SQL Get Total Column from Dynamic Pivot

I have problem with getting total column from my dynamic pivot query.
From my code i got this result :
the result without total
, I want the total column from column 32,33,34,35 and place it after column 35 (vertical total column)

Here is my code :

SET DATEFIRST 1;
DECLARE @cols AS NVARCHAR(MAX),
@colNames AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(DATEPART(wk,OINV.TaxDate))
FROM OINV
WHERE YEAR(OINV.TaxDate) = 2016 AND MONTH(OINV.TaxDate) = 8
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SELECT @colNames = STUFF((SELECT DISTINCT ', ISNULL(' + QUOTENAME(DATEPART(wk,OINV.TaxDate)) +', 0) as '+ QUOTENAME(DATEPART(wk,OINV.TaxDate))
FROM OINV
WHERE YEAR(OINV.TaxDate) = 2016 AND MONTH(OINV.TaxDate) = 8
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = 'SELECT Salesman, Cabang, '+ @colNames +'
FROM(SELECT
OSLP.SlpName as Salesman,
OCRD.U_STEM_BP_WHSE as Cabang,
SUM(OINV.DocTotal) as Achiev,
DATEPART(wk,OINV.TaxDate) as WeekNo
FROM OINV
INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry
INNER JOIN OSLP ON OINV.SlpCode = OSLP.SlpCode
INNER JOIN OITM ON INV1.ItemCode = OITM.ItemCode
INNER JOIN OMRC ON OITM.FirmCode = OMRC.FirmCode
INNER JOIN OCRD ON OINV.CardCode = OCRD.CardCode
WHERE
YEAR(OINV.TaxDate) = 2016 AND MONTH(OINV.TaxDate) = 8
GROUP BY
OSLP.SlpName,
OCRD.U_STEM_BP_WHSE,
OINV.TaxDate) AS a
PIVOT(SUM(Achiev)
FOR WeekNo IN (' + @cols + ')) AS pvt
ORDER BY Salesman ASC'

EXECUTE(@query);


Your help is much appreciated, thank you.

Use advice from @Anton, this is updated result i got :
the updated result

Answer

Change

SET @query =  'SELECT Salesman, Cabang, '+ @colNames +'

To

SET @query =  'SELECT Salesman, Cabang, '+ @colNames +', ' + @SumCol32_35 + '

@SubCol32_35 should be like '[32] + [33] + [34] + [35]'

You can hardcode it if the column names are fixed, or populate it inside

SELECT @colNames =  

with CASE ... END statement to populate relevant columns only.