Cross Tab Using CASE Expression, PIVOT, PIVOT with Dynamic Column Value in SQL SERVER 2005

Cross Tab Query or Report is one of the coolest features of Database Programming. Here, I am going to explain Cross Tab Query using CASE Expression and PIVOT. However, PIVOT is only available in SQL Server 2005 or later version.

Let’s create t_SaleReport table and insert some demo data in it.

CREATE TABLE t_SaleReport
(
SaleDate datetime NOT NULL,
CategoryName varchar(25) NOT NULL,
CategorySaleAmount bigint NOT NULL,
CONSTRAINT PK_t_SaleReport PRIMARY KEY CLUSTERED
(
SaleDate ,CategoryName
)
)

INSERT INTO t_SaleReport VALUES(GETDATE(),’FOOD’,2000)
INSERT INTO t_SaleReport VALUES(GETDATE(),’DRINKS’,5000)
INSERT INTO t_SaleReport VALUES(GETDATE(),’OTHERS’,2000)

INSERT INTO t_SaleReport VALUES(DATEADD(DAY,1,GETDATE()),’FOOD’,3000)
INSERT INTO t_SaleReport VALUES(DATEADD(DAY,1,GETDATE()),’DRINKS’,6000)
INSERT INTO t_SaleReport VALUES(DATEADD(DAY,1,GETDATE()),’OTHERS’,3000)

INSERT INTO t_SaleReport VALUES(DATEADD(DAY,2,GETDATE()),’FOOD’,3000)
INSERT INTO t_SaleReport VALUES(DATEADD(DAY,2,GETDATE()),’DRINKS’,6000)
INSERT INTO t_SaleReport VALUES(DATEADD(DAY,2,GETDATE()),’OTHERS’,3000

SELECT CONVERT(VARCHAR,SaleDate,103) as SaleDate, CategoryName,CategorySaleAmount FROM t_SaleReport

Cross Tab using CASE Expression:

SELECT CONVERT(VARCHAR,SaleDate,103) as SaleDate,
SUM(CASE CategoryName WHEN ‘FOOD’ THEN CategorySaleAmount END) as ‘FOOD’,
SUM(CASE CategoryName WHEN ‘DRINKS’ THEN CategorySaleAmount END) as ‘DRINKS’,
SUM(CASE CategoryName WHEN ‘OTHERS’ THEN CategorySaleAmount END) as ‘OTHERS’
FROM t_SaleReport
GROUP BY CONVERT(VARCHAR,SaleDate,103)

PIVOT Basic 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];

Cross Tab using PIVOT:

SELECT CONVERT(VARCHAR,SaleDate,103)as SaleDate,
[FOOD] as [FOOD], [DRINKS] as [DRINKS],[OTHERS] as [OTHERS]
FROM
(
SELECT CONVERT(VARCHAR,SaleDate,103) as SaleDate,CategoryName,CategorySaleAmount FROM t_SaleReport
)DataQuery
PIVOT
(
sum([CategorySaleAmount])for [CategoryName] IN ([FOOD],[DRINKS],[OTHERS])
)
as PivotResult
ORDER by SaleDate

Dynamic Value PIVOT:

If You want to write Cross Tab Query for Unknown values of Column, PIVOT is the only solution.

INSERT INTO t_SaleReport VALUES(DATEADD(DAY,3,GETDATE()),’FOOD’,3000)
INSERT INTO t_SaleReport VALUES(DATEADD(DAY,3,GETDATE()),’DRINKS’,6000)
INSERT INTO t_SaleReport VALUES(DATEADD(DAY,3,GETDATE()),’MOVIE’,3000)
INSERT INTO t_SaleReport VALUES(DATEADD(DAY,3,GETDATE()),’OTHERS’,3000)

SELECT CONVERT(VARCHAR,SaleDate,103) as SaleDate,CategoryName,CategorySaleAmount FROM t_SaleReport

This is how you can generate Cross Tab for unknown values of PIVOT Column.

DECLARE @CategoryNameValues as NVARCHAR(200);

SELECT @CategoryNameValues =
COALESCE(@CategoryNameValues + ‘,[‘ + a.CategoryName +’]’,
‘[‘ + a.CategoryName +’]’)
FROM (SELECT DISTINCT CategoryName FROM t_SaleReport) a
ORDER BY a.CategoryName ;

PRINT @CategoryNameValues;

DECLARE @QueryString as NVARCHAR(1000)

SET @QueryString =
‘SELECT CONVERT(VARCHAR,SaleDate,103)as SaleDate,’
+ @CategoryNameValues +
‘FROM
(SELECT CONVERT(VARCHAR,SaleDate,103) as SaleDate,CategoryName,CategorySaleAmount FROM t_SaleReport )DataQuery
PIVOT
(sum([CategorySaleAmount])for [CategoryName] IN (‘+@CategoryNameValues+’))
as PivotResult
ORDER by SaleDate’

EXECUTE(@QueryString)

Thanks
A Rahim Khan

Advertisements
    • Sajib
    • August 7th, 2010

    thanks for such concise and neat explanation.

  1. i want to make a hotel bill ,how i can make????????

  1. August 7th, 2010

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

%d bloggers like this: