SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 UnPivot a Pivoted SQL Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deanblaster
Starting Member

USA
2 Posts

Posted - 08/10/2008 :  17:03:46  Show Profile  Reply with Quote


Hi all... I'm new to this forum, and am a beginner to SQL querying.
Hopefully I can describe this in a way that makes sense.

There's a table I'm working with containing budgeting information that features a column for each month with a dollar amount.
I'd like to get a result set that has one line for each months amount.

Here's an example of the way it looks:

ACCOUNT_NAME, FISCAL_YEAR, BGT_SEPT, BGT_OCT, BGT_NOV
Travel Expenses, 2008, $1,500.00, $2,000.00, $2,500.00
Entertainment Expenses, 2008, $5,200.00, $5,350.00, $5,125.00

Here's an example of how I'd like my result set to look:

ACCOUNT_NAME, FISCAL_YEAR, FISCAL__PERIOD, BGT_AMT
Travel Expenses, 2008, 9, $1,500.00
Travel Expenses, 2008, 10, $2,000.00
Travel Expenses, 2008, 11, $2,500.00
Entertainment Expenses, 2008, 9, $5,200.00
Entertainment Expenses, 2008, 10, $5,350.00
Entertainment Expenses, 2008, 11, $5,125.00

Can someone please help me form a SELECT statement that would do this? I'm guessing I have to use a CASE statement.
Thanks in advance from a frustrated beginnner :)

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 08/10/2008 :  17:23:28  Show Profile  Reply with Quote
Sql2k5 has unpivot operator, read books online for details.
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 08/10/2008 :  18:09:13  Show Profile  Reply with Quote
A practical example of unpivot:
http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx
Note Jeff points out that this is compensating for a design problem (first normal form violation). Ideally your schema should be normalised in the first place:
http://www.tonymarston.net/php-mysql/database-design.html
Go to Top of Page

deanblaster
Starting Member

USA
2 Posts

Posted - 08/10/2008 :  20:07:24  Show Profile  Reply with Quote
Thank you both for the information.

pootle_flump.... Those links were exactly what I needed. Much appreciated.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000