How To Pivot Table In Sql Server With A Stored Procedure?

Original output: Desired output:

Solution 1:

Check this below example for your reference or else post your present output and required output.

/*

SELECT * INTO #tblStock
FROM
(
SELECT 'A' PartCode,  10 StockQty, 'WHs-A' Location
UNION ALL
SELECT 'B',   22,  'WHs-A'
UNION ALL
SELECT 'A',   1,   'WHs-B'
UNION ALL
SELECT 'C',   20,  'WHs-A'
UNION ALL
SELECT 'D',   39,  'WHs-F'
UNION ALL
SELECT 'E',   3,   'WHs-D'
UNION ALL
SELECT 'F',   7,   'WHs-A'
UNION ALL
SELECT 'A',   9,   'WHs-C'
UNION ALL
SELECT 'D',   2,   'WHs-A'
UNION ALL
SELECT 'F',   54,  'WHs-E'
)TAB

REQUIRED OUT PUT

+-------------+-------+-------+-------+-------+-------+-------+-------+
|  PARTCODE   | WHs-A | WHs-B | WHs-C | WHs-D | WHs-E | WHs-F | TOTAL |
+-------------+-------+-------+-------+-------+-------+-------+-------+
| A           |    10 |     1 |     9 |     0 |     0 |     0 |    20 |
| B           |    22 |     0 |     0 |     0 |     0 |     0 |    22 |
| C           |    20 |     0 |     0 |     0 |     0 |     0 |    20 |
| D           |     2 |     0 |     0 |     0 |     0 |    39 |    41 |
| E           |     0 |     0 |     0 |     3 |     0 |     0 |     3 |
| F           |     7 |     0 |     0 |     0 |    54 |     0 |    61 |
| GRAND_TOTAL |    61 |     1 |     9 |     3 |    54 |    39 |   167 |
+-------------+-------+-------+-------+-------+-------+-------+-------+

*/DECLARE@COLUMNS NVARCHAR(MAX),@COLUMNS_ISNULL NVARCHAR(MAX),@SQLQUERY NVARCHAR(MAX),@COLUMNS_TOTAL NVARCHAR(MAX),
@GRNADTOTAL NVARCHAR(MAX),@GRNADTOTAL_SUM NVARCHAR(MAX)

 SET@COLUMNS= stuff((SELECTdistinct',['+location +']'FROM #tblstock for xml path('')),1,1,'')

SET@COLUMNS_ISNULL=  stuff((SELECTdistinct',ISNULL(['+location +'],0) AS ['+location +']'FROM #tblstock for xml path('')),1,1,'')


SET@COLUMNS_TOTAL=stuff((SELECTdistinct'+ISNULL(['+location +'],0)'FROM #tblstock for xml path('')),1,1,'')


--SET @COLUMNS_TOTAL=','+@COLUMNS_TOTAL+'as total 'SET@GRNADTOTAL= stuff((SELECTdistinct',SUM(ISNULL(['+location +'],0))'FROM #tblstock for xml path('')),1,1,'')

set@GRNADTOTAL_SUM='SUM('+@COLUMNS_TOTAL+')'select@COLUMNS_TOTAL


SET@SQLQUERY=
N'
SELECT partcode, '+@COLUMNS_ISNULL +','+@COLUMNS_TOTAL +N'as total FROM(
SELECT * FROM #TBLSTOCK
)AS A
PIVOT (MAX(STOCKQTY) FOR LOCATION IN('+@COLUMNS+N')
) AS A'

PRINT @SQLQUERYEXEC SP_EXECUTESQL @SQLQUERYSET@SQLQUERY=
N'
;with cte as(
SELECT partcode, '+@COLUMNS_ISNULL +','+@COLUMNS_TOTAL +N'as total FROM(
SELECT * FROM #TBLSTOCK
)AS A
PIVOT (MAX(STOCKQTY) FOR LOCATION IN('+@COLUMNS+N')
) AS A
)
select PARTCODE, '+@COLUMNS_ISNULL+','+@COLUMNS_TOTAL +N'AS TOTAL from cte
union all
select ''GRAND_TOTAL'', '+@GRNADTOTAL+','+@GRNADTOTAL_SUM+N'from cte'

PRINT @SQLQUERYEXEC SP_EXECUTESQL @SQLQUERY

Solution 2:

Finally I found the answer

Used some temporary tables

select * 
into #tmpFROM

(

select YearData, value, col

from#tmp

unpivot

(

  value
  for col in 
([NetSales],[TotalExpenditure],[EBIDTA],[DeprecAmortization],[EBIT],[InterestExpense],[OtherIncome],[EBT],[Exceptionals],[APAT],[Sharecapital],[ReservesSurplus],[TotalNetworth],[TotalDebt],[NetBlock],[CapitalWip],[Investments],[TotalCurrentAssets],[TotalCurrentLiabilities],[NetCurrentassets],[NetDeferredTax],[OtherAssets])

) unpiv

 ) as b

pivot

(

  max(value)

  for YearData in ([2015],[2014],[2013],[2012])

) piv;