CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Theva Deva
Theva Deva

Reputation: 63

Row and column total in dynamic pivot with multiple Select column

In SQL Server 2008, I have a table tblStock with 4 columns:

PartCode (NVARCHAR (50)) 
Role (NVarchar(10)) 
StockQty (INT) 
Location (NVARCHAR(50))

Some example data below:

Data From Temporary Table

I have written this code to get the following output:

IF OBJECT_ID('tempdb..#tblData') IS NOT NULL 
    DROP TABLE #tblData 

SELECT * 
INTO #tblData 
FROM 
    (SELECT 'A' PartCode, 'Manager' As [Role],  10 StockQty, 'in-A' Location
     UNION ALL
     SELECT 'B', 'Director' As [Role], 22, 'in-A'
     UNION ALL
     SELECT 'A', 'Director' As [Role], 1, 'in-B'
     UNION ALL
     SELECT 'C', 'Director' As [Role], 20, 'in-A'
     UNION ALL
     SELECT 'D', 'Director' As [Role], 39, 'in-F'
     UNION ALL
     SELECT 'E', 'Director' As [Role],  3,   'in-D'
     UNION ALL
     SELECT 'F', 'Director' As [Role],  7,   'in-A'
     UNION ALL
     SELECT 'A', 'Director' As [Role],  9,   'in-C'
     UNION ALL
     SELECT 'D', 'Director' As [Role],  2,   'in-A'
     UNION ALL
     SELECT 'F', 'Director' As [Role],  54,  'in-E') TAB

SELECT * 
FROM #tblData

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + Location + ']', '[' + Location + ']')
               FROM (SELECT DISTINCT Location FROM #tblData) PV 
               ORDER BY Location 

SELECT @cols += ',[Total]' 

DECLARE @NulltoZeroCols NVARCHAR (MAX) 
SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+Location+'],0) AS ['+Location+']' 
FROM (SELECT DISTINCT Location FROM #tblData)TAB  
ORDER BY Location FOR XML PATH('')),2,8000) 

SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT PartCode, Role, ' + @NulltoZeroCols + ' FROM 
             (
                 SELECT 
                 ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode, 
                 Role,
                 SUM(StockQty)StockQty , 
                 ISNULL(Location,''Total'')Location              
                 FROM #tblData 
                 GROUP BY Location,PartCode,Role 
                 WITH CUBE
             ) x
             PIVOT 
             (
                 MIN(StockQty)
                 FOR Location IN (' + @cols + ')
            ) p
            ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode' 

EXEC SP_EXECUTESQL @query

Result for the above query is:

Output With Errors

I want to get the result like this:

Correct Image

Please share your ideas - thanks!

Upvotes: 1

Views: 246

Answers (3)

vsskumaravel
vsskumaravel

Reputation: 1

DECLARE @query NVARCHAR(MAX) SET @query = 'SELECT p.PartCode, (SELECT TOP 1 Role FROM #tblData t3 WHERE t3.PartCode = p.PartCode and t3.Role = t2.Role ) Role, ' + @NullToZeroCols +', ('+ @SumCols+' FROM ( SELECT ISNULL(CAST(t1.PartCode AS VARCHAR(30)),''Total'')PartCode, -- Role, SUM(StockQty)StockQty , ISNULL(Location,''Total'')Location
FROM #tblData t1 GROUP BY Location,t1.PartCode WITH CUBE ) x PIVOT ( MIN(StockQty) FOR Location IN (' + @cols + ') ) p left JOIN #tblData t2 on t2.PartCode = p.PartCode GROUP BY t2.Role,p.PartCode, ' + @cols +' ORDER BY CASE WHEN (p.PartCode=''Total'') THEN 1 ELSE 0 END,p.PartCode' PRINT @query EXEC (@query)

Upvotes: 0

Theva Deva
Theva Deva

Reputation: 63

Updated part will be,

SET @query = 'SELECT PartCode, Role, ' + @NulltoZeroCols + ' FROM 
             (
                SELECT PartCode, Role, StockQty, Location
                FROM #tblData
                UNION ALL

                SELECT PartCode, Role, SUM(StockQty), ''Total'' as Location
                FROM #tblData
                GROUP BY PartCode, Role            

                UNION ALL

                  SELECT 
                 ''Total'' PartCode, 
                 ''Total'' Role, 
                 SUM(StockQty)StockQty , 
                 ISNULL(Location,''Total'')Location              
                 FROM #tblData
                 GROUP BY Location   
                 WITH cube
             ) x
             PIVOT 
             (
                 MIN(StockQty)
                 FOR Location IN (' + @cols + ')
            ) p
            ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode'

Upvotes: 1

Yunus UYANIK
Yunus UYANIK

Reputation: 249

Not MIN, use SUM. Also, you should not select the Role column.

Try this;

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + Location + ']', '[' + Location + ']')
               FROM (SELECT DISTINCT Location FROM #tblData) PV 
               ORDER BY Location 

SELECT @cols += ',[Total]' 

DECLARE @NulltoZeroCols NVARCHAR (MAX) 
SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+Location+'],0) AS ['+Location+']' 
FROM (SELECT DISTINCT Location FROM #tblData)TAB  
ORDER BY Location FOR XML PATH('')),2,8000) 

DECLARE @SumCols NVARCHAR (MAX) 
SELECT @SumCols = SUBSTRING((SELECT '+(ISNULL(['+Location+'],0))' 
FROM (SELECT DISTINCT Location FROM #tblData)TAB  
ORDER BY Location FOR XML PATH('')),2,8000) 
PRINT @SumCols

SET @SumCols += ') AS [Total]'

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT PartCode, (SELECT TOP 1 Role FROM #tblData WHERE PartCode=p.PartCode) Role, ' + @NulltoZeroCols +', ('+ @SumCols+' FROM 
             (
                 SELECT 
                 ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode, 

                 SUM(StockQty)StockQty , 
                 ISNULL(Location,''Total'')Location              
                 FROM #tblData 
                 GROUP BY Location,PartCode 
                 WITH CUBE
             ) x
             PIVOT 
             (
                 MIN(StockQty)
                 FOR Location IN (' + @cols + ')
            ) p
            ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode' 
PRINT @query
EXEC SP_EXECUTESQL @query

Upvotes: 1

Related Questions