Wednesday, 12 June 2013

Pivot Example in SQL Server

Hi All, I got the scenario where i need to present the row data as column header in SQL server 2008. My scenario might be bit different. Please have a look.













I need to present the month column data row wise corresponding to all the Fields
([AC_Metrics As Euro],[AC_Metrics As Units],[AC_Metrics As LC],[BD_Metrics As Euro],[BD_Metrics As Units],[BD_Metrics As LC]). According to my requirement data should be appear like -










In the above image, data is same as the original data, but the presentation style of the data has been changed. In the above image all months from the row data became column header and other corresponding details (Euro and Units) became the value(total sum).
So my requirement has been full filled. I tried to pivot the SQL table but in different way. 
I recognized that if there are two column then easily we can do the  pivoting but when there are more than two columns then pivoting might be little bit difficult. 
To implement this scenario i used pivoting after implementing the union and few temporary tables.

Step 1: Get all the data from the original table and insert into a temporary table.
SELECT  * into #Temp1 from YourTable

Step 2: Select required data from the temporary table and implement union as below after that insert this data into an another temporary table -

















Step 3: Declare variables as below - 
     DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

 Step 4: Assign the value to @cols and @query 
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(MonthYear) 
                    from #Temp2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
set @query = 'SELECT' + @cols + '
            from 
            (
                select MonthYear,Amount
                from #Temp2
            ) x
            pivot 
            (
                max(Amount)
                for MonthYear in (' + @cols + ')
            ) p '


Step 5: Execute the query and drop the temporary tables
execute(@query)     
drop table #Temp1
drop table #Temp2


In this way i got the result for the required scenario.






No comments:

Post a Comment