I'm thinking I need to use PIVOT but not sure how

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

SQL Server 2005 Forums

Transact-SQL (2005)

I'm thinking I need to use PIVOT but not sure how

Author

Topic

bmturney
Starting Member

7 Posts

Posted - 2009-03-31 : 16:41:49

I have a table that is basically laid out like this

Customer ID Billing_month
1 2008/12
1 2008/11
1 2008/10
2 2008/12
2 2008/11
2 2008/09
3 2008/11
4 2008/12
4 2008/11

I would like to group the data by customer ID, but layout the last 12 billing months horizontally in columns

I can get the billing months I need for each customer by running the SQL Statement:

Select top 12 billing_month from table1 where customerID = x order by billing_month desc

but I would like to be able to pull the last 12 billing months for each customer all at the same time and where they are laid out horizontally like the following

CustomerID MONTH01 MONTH02 MONTH03 MONTH04 .... etc.....
1 2008/12 2008/11 2008/10
2 2008/12 2008/11 2008/09
3 2008/11
4 2008/12 2008/11

So that it list the data for each customer where MONTH01 date of the last bill, MONTH02 is the date of the bill before that, and MONTH03 is the date of the bill before that, etc.... all the way back for the last 12 billing cycles for each customer

I'm thinking the PIVOT function is the way to go, but since I'm not really aggregating anything, I'm wondering if that will really work.

Any ideas would be greatly appreciated.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-31 : 16:46:40

If that is the required layout then I would first add a row_Number() partitioned by groupID with desc order by the month.

Then once you do that you can either create a pivot query on the rowNumber column, or you can just write out the query using case statements.

If you need help in doing this please let me know and I will see what I can do.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

bmturney
Starting Member

7 Posts

Posted - 2009-03-31 : 16:56:09

quote:
Originally posted by Vinnie881

If that is the required layout then I would first add a row_Number() partitioned by groupID with desc order by the month.

Then once you do that you can either create a pivot query on the rowNumber column, or you can just write out the query using case statements.

If you need help in doing this please let me know and I will see what I can do.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

I'm not sure I follow you, what do you mean "add a row_Number() partitioned by groupID with desc order by the month."

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-31 : 16:59:32

Select Row_Number() over (Partion By CustomerID order by BillingMonth Desc) as ROwID,*

That will give a ID# to each date for a CustomerID starting with the most recent being 1.

This allows you to then build a query where RowID <= 12 to get your last 12 billing months for each CustomerID

Once you have that, you can then just create a pivot table to put each rowID into a column (Columns 1-12) and name it what you want to give you your desired results.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

bmturney
Starting Member

7 Posts

Posted - 2009-03-31 : 17:14:21

OK... I gotcha... Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources