How to return months for column lbls in crosstab? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to return months for column lbls in crosstab?

Author

Topic

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-08-01 : 09:39:13

I want to line up across the top of a cross tab Jan-12 Feb-12 ... July-13 Aug-13 up to and including the latest month in the db.
What's the best way to achieve this?

I currently create crosstabs with case statements as follows:


select
     sum(case month(date) when 1 then value) as Jan,
     sum(case month(date) when 2 then value) as Feb
from db

I figured you could create some sort of while loop for the case statements but I can't get the syntax right.

Or another strategy would be to use the Pivot method and build [Jan], [Feb], [Mar] etc through a loop?

Any help appreciates.

WAmin
Starting Member

16 Posts

Posted - 2013-08-01 : 10:12:57

1- Do you know how many months/years are there before you generate this report? If you don't have that information, how are you going to use case statement?
I am bit confused on your requirement but here u go

1- I create a temp table which will hold your calculated information
2- Then i ran a loop on temp table to convert months-year to column heading
3- displayed the information
4- delete tmp table.

CREATE TABLE #tmpdb (Value MONEY,ColumnHeading VARCHAR(8),ID INT IDENTITY(1,1))

;WITH xdb AS (
SELECT
SUM(Value) Value,MONTH(Date) MNT,DATENAME(MONTH,Date) MntName, YEAR(Date) YR
FROM
db
GROUP BY
MONTH(Date),DATENAME(MONTH,Date),YEAR(Date))

INSERT INTO #tmpdb SELECT Value,SUBSTRING(MntName,1,3) + '-' + CONVERT(VARCHAR,YR) FROM xdb

DECLARE @cREC INT,@eREC INT
SELECT @cREC=1,@eREC=COUNT(*) FROM #tmpdb

DECLARE @SQL VARCHAR(MAX)
SET @SQL=''

WHILE @cREC<=@eREC
BEGIN
SELECT @SQL=@SQL + CASE WHEN @SQL<>'' THEN ',' ELSE '' END + ' (SELECT '''+ CONVERT(VARCHAR,Value,10) + ''') ['+ColumnHeading+']' FROM #tmpDB WHERE ID=@cREC
SET @cREC=@cREC+1
END
SET @SQL='SELECT ' + @SQL
EXEC (@SQL)
DROP TABLE #tmpdb

Here is what i am getting

Jan-2012 Feb-2012 Mar-2012 Apr-2012 May-2012 Jun-2013 Jul-2013 Aug-2013 Sep-2013 Oct-2013
-------- -------- -------- -------- -------- -------- -------- -------- -------- --------
4,000.00 1,000.00 2,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00

(1 row(s) affected)

BTW here is my sample data

Value Date
--------------------- -----------------------
1000.00 2012-01-01 00:00:00.000
1000.00 2012-02-01 00:00:00.000
1000.00 2012-03-01 00:00:00.000
1000.00 2012-04-01 00:00:00.000
1000.00 2012-05-01 00:00:00.000
1000.00 2013-06-01 00:00:00.000
1000.00 2013-07-01 00:00:00.000
1000.00 2013-08-01 00:00:00.000
1000.00 2013-09-01 00:00:00.000
1000.00 2013-10-01 00:00:00.000
1000.00 2012-01-01 00:00:00.000
1000.00 2012-01-02 00:00:00.000
1000.00 2012-01-03 00:00:00.000
1000.00 2012-03-01 00:00:00.000

(14 row(s) affected)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-01 : 10:54:57

If you know in advance how many columns you will have and what they are, then PIVOT operator should be better than using a while loop. If you don't have that information a'priori, then you can use dynamic pivoting. Madhivanan's blog has code and examples: http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 12:32:42

If the intention behind this is for generating report, you can also achieve this in most of reporting tools using crosstabbing feature.
In SQL reporting Services, you've matrix container for this purpose. Choose column group as date field and it will automatically generate columns for each date values present in query with required aggregated data shown as per expression applied,

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources