SQL SERVER: Cross tab query with PIVOT

We had a User group meeting for "Cross Tab Queries", where I have explained how to write PIVOT queries. 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

 

Let me know if you have any question/comment.

Reference: Tejas Shah (www.SQLYoga.com)