Saturday, 16 July 2016

Pivot Table in sql server 2012




PIVOT is relational operator which is introduced in Sql Server 2005. It provides a simple mechanism in which you can transform Sql Server rows into columns.

PIVOT show the data in a table-valued expression having unique values from one column in the expression into multiple columns in the output, and also performs aggregations function where they are requires in any remaining column as final output.

Syntax

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
Basic Pivot example:

Select * from tbl_ExchangeRate 

 



Select * from tbl_currency

 

Final Output
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.currencyid)
            FROM tbl_currency c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
set @query = 'SELECT date, ' + @cols + ' from
            (
                select date
                    , BuyRate,currencyID
                from tbl_ExchangeRate
           ) x
            pivot
            (
                 max(BuyRate)
                for currencyID in (' + @cols + ')
            ) p '
execute(@query)

 

Merge two pivot table using Join.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.currencyid)
            FROM tbl_currency c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
set @query = 'SELECT date, ' + @cols + '
into ##tmp from
            (
                select date
                    , BuyRate,currencyID
                from tbl_ExchangeRate
           ) x
            pivot
            (
                 max(BuyRate)
                for currencyID in (' + @cols + ')
            ) p '
execute(@query)
DECLARE @cols1 AS NVARCHAR(MAX),
    @query1  AS NVARCHAR(MAX);

SET @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(c.currencyid)
            FROM tbl_currency c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
set @query1 = 'SELECT convert(varchar(11),date,103) as date, ' + @cols1 + '
into ##tmp2 from
            (
                select convert(varchar(11),date,103)as date
                    , SellRate,currencyID
                from tbl_ExchangeRate
           ) x
            pivot
            (
                 max(SellRate)
                for currencyID in (' + @cols1 + ')
            ) p '
execute(@query1)

SELECT ##tmp2.date,##tmp.[1] as Buy_D,##tmp2.[1] as Sell_D,
##tmp.[2] as Buy_A,##tmp2.[2] as Sell_A,
##tmp.[3] as Buy_P,##tmp2.[3] as Sell_P,
##tmp.[4] as Buy_I,##tmp2.[4] as Sell_I,
##tmp.[5] as Buy_E,##tmp2.[5] as Sell_E
 FROM ##tmp JOIN ##tmp2
ON ##tmp.DATE=##tmp2.DATE

  

I hope that the functionality of the above attribute is clear to you now. Now you can use this code in your application If you have further question, just drop a line below and I will try to answer you as soon as possible.
Last but not least, connect with me on Twitter , Facebook , LinkedIn and Google+ for technical updates and articles news. We won’t spam or share your email address as we respect your privacy.


No comments:

Post a Comment