Transpose Columns Into Rows - 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

SQL Server Development (2000)

Transpose Columns Into Rows

Author

Topic

MeltdownMX
Starting Member

2 Posts

Posted - 2005-07-13 : 10:18:08

Hi,

I saw one other post on this however what was needed to be accomplished was much too complicated for what I need to do. I have a query which returns one row of data and about 10 columns. I want to chart the data using a pie chart with Coldfusion. The problem I have is that if I want to present the data appropriately I need the Row that is returned to become a column and all of the column names to become a bunch of records under a column name. Here is my example:

I currently have:

RegularHours OTHours TrainingHours TrainingOT VacationHours
5,000        1,000   500           25         250

I want it to look like this:

Hours     PayType
5,000     RegularHours
1,000     OTHours
500       TrainingHours
25        TrainingOT
250       VacationHours

I dont think its that hard. I mean I know how to do it in MS excel, however I am not sure how to do this with SQL and do not want to be manually exporting to excel, transposing, then reimporting into SQL all the time. How can this be done with one row or a few rows of data?
Any help would be appreciated. Thanks in advance!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-13 : 10:56:34

use union all:

select 5,000 as hours, 'RegularHours' as PayType union all
select 1,000, 'OTHours' union all
select 500, 'TrainingHours' union all
select 25, 'TrainingOT' union all
select 250, 'VacationHours' union all

Go with the flow & have fun! Else fight the flow

MeltdownMX
Starting Member

2 Posts

Posted - 2005-07-13 : 14:23:59

Hi spirit1,

Thanks for that input, I couldnt get the UNION ALL to work like you posted, it kept complaining about incorrect syntax near the FROM clause. But I finally did figure it out. I am not an DB Admin by trade, just a network admin with web development and database experience.

So this is what I am using to get it to work. I will post it in case anyone else needs to do the same thing. I have been looking for a way to do this and this looked like the best resource in getting help to figure this out.


SELECT     TotalReg AS Hours, 'Regular Hours' AS PayType
FROM         dbo.FY05_Workforce
UNION ALL
SELECT     TotalOT, 'Regular OT'
FROM         dbo.FY05_Workforce
UNION ALL
SELECT     TrainReg, 'Training Regular'
FROM         dbo.FY05_Workforce
UNION ALL
SELECT     LeaveReg, 'Regular Leave'
FROM         dbo.FY05_Workforce
UNION ALL
SELECT     LeaveOT, 'Leave OT'
FROM         dbo.FY05_Workforce
UNION ALL
SELECT     TrainOT, 'Training OT'
FROM         dbo.FY05_Workforce
UNION ALL
SELECT     WBReg, 'WB Regular Hrs'
FROM         dbo.FY05_Workforce
UNION ALL
SELECT     WBOT, 'WB OT Hrs'
FROM         dbo.FY05_Workforce
UNION ALL
SELECT     TotalHrsAvail, 'Total Hours Avail'
FROM         dbo.FY05_Workforce
UNION ALL
SELECT     TotalHrsPAID, 'Total Hours Paid'
FROM         dbo.FY05_Workforce

Thanks a LOT spirit1. You Rock!!!

tking1
Starting Member

22 Posts

Posted - 2010-05-24 : 17:40:21

Meltdown,

Thanks for this post, it was exactly what I needed and saved me alot of heartache!

TK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources