Converting Rows into Columns

    In SQL Server, you may require to convert or transpose all the rows into Columns and then display the reports. This can be achieved easily with the help of Excel or any thing else. And in SQL using PIVOT we can achive it partially of two columns with some aggregate function.
    But it comes complex, when we have many columns to transpose. To make this we need to use both the functions PIVOT and UNPIVOT in our script. Let's take an example and understand here :

Let's say our first report is produced by the simple query below :

SELECT  TradeYear, UserID, SUM(BuyQty) AS BuyQty

  ,SUM(BuyPrice) AS BuyPrice,SUM(SaleQty) AS SaleQty

  ,SUM(SalePrice) AS SalePrice

FROM   UserTrades

GROUP BY UserID, TradeYear

WHERE  UserID = 'U001'

And we will get the output as :

 
 
 
 
 


  
TradeYear
  
UserID
  
BuyQty
  
BuyPrice
  
SaleQty
  
SalePrice
 

  
2001
  
U001
  
150
  
85
  
145
  
90
 

  
2002
  
U001
  
420
  
27
  
410
  
30
 

  
2003
  
U001
  
345
  
60
  
240
  
70
 

  
2004
  
U001
  
600
  
75
  
585
  
90
 

  
2005
  
U001
  
280
  
110
  
250
  
120
 

  
2006
  
U001
  
240
  
55
  
225
  
75
 

   But the Business Analyst or the Higher level Management want to see the report showing TradeYears as the Columns Headers. See below report :

 

TradeYear
2001
2002
2003
2004
2005
2006
UserID
U001
U001
U001
U001
U001
U001
BuyQty
150
420
345
600
280
240
BuyPrice
85
27
60
75
110
55
SaleQty
145
410
240
585
250
225
SalePrice
90
30
70
90
120
75

     If it was to sum any of the BuyQty or BuyPrice or SaleQty or SalePrice one at a time, then it became very simple and could be achieved with the help of PIVOT.

      Since here we are converting all the rows into columns and vice-versa, so we need to do it as below :

---- Create a temp table #Data to insert the complete data of UserTrades.






CREATE TABLE #Data

            (
 


                         

[TradeYear]       VARCHAR(100)


            ,[UserID]           VARCHAR(100)


            ,[BuyQty]          VARCHAR(100)


            ,[BuyPrice]        VARCHAR(100)


            ,[SaleQty]         VARCHAR(100)


            ,[SalePrice]       VARCHAR(100)


            )

      INSERT INTO
#Data

      SELECT  * FROM  UserTrades


----  Create a temp table #Columns to insert all the columns of UserTrades.





      IF OBJECT_ID('TEMPDB..#Columns') IS NOT NULL

            DROP TABLE #Columns



      SELECT  ORDINAL_POSITION

              ,COLUMN_NAME

      INTO    #Columns

      FROM    TEMPDB.INFORMATION_SCHEMA.COLUMNS
C 

      WHERE   C.TABLE_NAME LIKE '#Data%'

      ORDER BY
C.ORDINAL_POSITION


----- Take all the possible TradeYear into a variable @CList.





DECLARE @ColList NVARCHAR(MAX)

SELECT @ColList=STUFF((SELECT '],['+CONVERT(VARCHAR,[TradeYear])



                             FROM #Data

                             FOR XML PATH('')), 1, 2, '') + ']'




----- Now write a dynamic query to get the report, as per our requirement.





      DECLARE @SQL NVARCHAR(MAX)



      IF OBJECT_ID('TEMPDB..#Reports') IS NOT NULL

            DROP TABLE #Reports



            SET @SQL = N'

            SELECT  P.*

            FROM    #Data D

            UNPIVOT (VALUE FOR [TradeYear] IN 

            (

             [UserID]

            ,[BuyQty]

            ,[BuyPrice]

            ,[SaleQty]

            ,[SalePrice]

            )) U

            PIVOT   (MAX(U.VALUE) FOR U.[TradeYear] IN ('+
@ColList + ')) P

            INNER JOIN #Columns C

            ON P.[Months] = C.COLUMN_NAME 

            ORDER BY C.ORDINAL_POSITION'



            EXEC  (@SQL)

And you are done here .....



Reference: Govind Badkur(http://govindbadkur.blogspot.com)