Generation Cross tab results without using PIVOT in all versions of Sql server
One of my friends asked me to write a query to get the cross tabs(displaying one of the Column’s Values as columns of a result set /Pivot).
Here are the inputs.
CREATE TABLE [dbo].[SalesOrderDetails]( [SalesOrderDetailsId] [bigint] NOT NULL, [SalesOrderId] [bigint] NOT NULL, [ShippedItems] [int] NOT NULL, [ShippedDate] [datetime] NOT NULL, CONSTRAINT [PK_SalesOrderDetails] PRIMARY KEY CLUSTERED ( [SalesOrderDetailsId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[SalesOrderDetails] ([SalesOrderDetailsId], [SalesOrderId], [ShippedItems], [ShippedDate]) VALUES (1, 1000, 300, CAST(0x0000A08100000000 AS DateTime)) (2, 1001, 400, CAST(0x0000A08100000000 AS DateTime)), (3, 1002, 200, CAST(0x0000A08100000000 AS DateTime)), (4, 1000, 720, CAST(0x0000A08200000000 AS DateTime)), (5, 1001, 560, CAST(0x0000A08200000000 AS DateTime)), (6, 1002, 310, CAST(0x0000A08200000000 AS DateTime)), (7, 1004, 210, CAST(0x0000A08600000000 AS DateTime)), (8, 1005, 222, CAST(0x0000A08700000000 AS DateTime)), (9, 1001, 800, CAST(0x0000A08200000000 AS DateTime))
Now the rows in the table are ..
SalesOrderDetails SalesOrderId ShippedItems ShippedDate
1 1000 300 2012-07-01 00:00:00.000 2 1001 400 2012-07-01 00:00:00.000 3 1002 200 2012-07-01 00:00:00.000 4 1000 720 2012-07-02 00:00:00.000 5 1001 560 2012-07-02 00:00:00.000 6 1002 310 2012-07-02 00:00:00.000 7 1004 210 2012-07-06 00:00:00.000 8 1005 222 2012-07-07 00:00:00.000 9 1001 800 2012-07-02 00:00:00.000
He wants me to generate the result set like this.
SalesOrderId 01/07/2012 02/07/2012 06/07/2012 07/07/2012 1000 300 720 0 0 1001 400 1360 0 0 1002 200 310 0 0 1004 0 0 210 0 1005 0 0 0 222
I wrote the query using PIVOT and sent it to him. He asked me to write the another query without using PIVOT so that the query can run on any version of the SQL SERVER to generate above result set. I started writing the query with all possibilities, realized that it is bit difficult
to write in a single query.
I wrote like this(using SQL SERVER 2008)
DECLARE @strQuery VARCHAR(MAX)
SELECT @strQuery ='SELECT SalesOrderID,'+ STUFF((select ',SUM(CASE WHEN CONVERT(char(10),ShippedDate,103) ='''+
CONVERT(char(10),ShippedDate,103) +''' THEN ShippedItems ELSE 0 END) AS '''+
CONVERT(char(10),ShippedDate,103) +''' ' FROM SalesOrderDetails
group by ShippedDate order by ShippedDate FOR XML PATH('')),1,1,'') + ' FROM SalesOrderDetails GROUP BY SalesOrderID'
EXEC(@strQuery)
After that I googled and I came to know that SQL SERVER 2000 doesn’t support this FOR XML PATH mode. I changed my DB compatability from 2008 to 2000 and ran the query. Surprisingly it ran successfully. But I didn’t want to take the risk. So I removed PATH mode and I changed my query like this.
DECLARE @strQuery varchar(max) SELECT @strQuery = 'SELECT SalesOrderID'+ REPLACE(REPLACE( CAST ((SELECT N',SUM(CASE WHEN CONVERT(char(10),ShippedDate,103) = ''' + CA.date_string + ''' THEN ShippedItems ELSE 0 END) AS ' + QUOTENAME(CA.date_string) as StrQuery FROM ( -- Unique dates in the unpivoted set SELECT ShippedDate, CONVERT(char(10), ShippedDate, 103) AS date_string FROM SalesOrderDetails AS U GROUP BY ShippedDate --Note: here we can join the above result set to another table to get more details and we can put filters --if needed. ) AS CA ORDER BY CA.ShippedDate FOR XML RAW ) as varchar(MAX)),'','') +' FROM SalesOrderDetails GROUP BY SalesOrderID' EXEC(@strQuery)
NOTE: SQL SERVER 2000 doesn’t support PIVOT,FOR XML PATH and CROSS APPLY.
Happy Coding.