Transposing Data Problem - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Transposing Data Problem

Author

Topic

gmcgahey
Starting Member

13 Posts

Posted - 2006-05-24 : 11:42:22

Hello
I need some help transposing data for reporting purposes.
Scenario is as follows:

I have an existing database table with the following format:
AccountID Budget1 Budget2 Budget3
GL980 6,090 7,000 4,700

I need to transpose this data into the following format:
AccountID Period Budget
GL980 1 6,090
GL980 2 7,000
GL980 3 4,700

where I 'Period' is a parameter, i.e. if Period 5 is selected, the output should show all periods from 1-5

There are 13 budgetary periods in the existing table structure.

Any help would be greatly appreciated!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-24 : 11:49:01

The best solution: Normalise your table existing database table structure.
http://www.datamodel.org/NormalizationRules.html

The temporary solution (something based on):

--data
declare @t table (AccountID varchar(10), Budget1 int, Budget2 int, Budget3 int)
insert @t select 'GL980', 6090, 7000, 4700

--calculation
select * from (
    select AccountID, 1 as Period, Budget1 from @t union all
    select AccountID, 2, Budget2 from @t union all
    select AccountID, 3, Budget3 from @t
    ) a
where Period <=2

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

gmcgahey
Starting Member

13 Posts

Posted - 2006-05-24 : 11:57:39

Thanks Ryan
Yes I wish the database was normalised, but its not within my control. The Union select will do just fine
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources