Cross tab query with PIVOT

Many times developer has an issue while writing query with PIVOT. Let me have a sample code for query with PIVOT keyword.

PIVOT is used to transform rows to column, which gives result in such a way which can be send to the user directly. Let me take an example. We have an order table where we have each order stored in a row in a table as displayed below:

ctrl_no id Amount OrderDate
1000029 100016 990 10/25/2012
1000029 100018 990 10/25/2012
1000029 100016 660 10/26/2012
1000029 100018 660 10/26/2012
1000029 206007 660 10/26/2012
1000029 206007 660 10/27/2012

This information has to be saved in a row in relational database. When this information needs to be display it on the application, it needs to be displayed as below, so user can use this info for decision making:

id 10/25/2012 10/26/2012 10/27/2012
100016 990.00 660.00 NULL
100018 990.00 660.00 NULL
206007 NULL 660.00 660.00

Did you see the difference? how easy user can review the information. Lets see an query how to achieve the expected result with PIVOT keyword.

CREATE TABLE #TestTable(ctrl_no INT, id int, Amount NUMERIC(18,2), OrderDate DATE)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

SELECT    id,
        [10/25/2012],[10/26/2012],[10/27/2012]
FROM (
    SELECT    id,
            Amount,
            OrderDate
    FROM #TestTable t
) x
PIVOT(
    SUM(Amount) FOR OrderDate IN([10/25/2012],[10/26/2012],[10/27/2012])
) p
 DROP TABLE #TestTable

Reference: www.SQLYoga.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s