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.
| Author |
Topic |
|
mparter
Yak Posting Veteran
86 Posts |
 Posted - 2004-11-23 : 10:35:08
|
I have a query that currently outputs the following (truncated):-Year Apps Interviews----------------------------2003/4 3640 1671 2004/5 3869 2183I'd like this to output in the following format:-Year Stage Value-------------------------2003/4 Apps 36402003/4 Interviews 16712004/5 Apps 38692004/5 Interviews 2183I've been scratching my head on this for the past couple of hours. Any pointers/ideas greatly appreciated? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-23 : 10:46:07
|
you just need to go through it twice, with a UNION, each time taking the column desired and returning the proper string literal in the Stage column:select Year, 'Apps' as Stage, Appsfrom YourTableUNION ALLselect Year, 'Interviews' as Stage, Interviewsfrom YourTable you could also use a cross join:[code]select Year, Stage, case stage when 'Apps' then Apps else Interviews End As Valuefrom YourTablecross join (select 'Apps' as Stage union all select 'Interviews') Stages[/end]- Jeff |
 |
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-11-23 : 10:54:21
|
| [code]set nocount ondeclare @t table (YearN char(7), Apps int, Interviews int)insert into @tSelect '2003/4', 3640, 1671 unionSelect '2004/5', 3869, 2183Select YearN, 'Applications' as Stage, apps as Value from @t UnionSelect YearN, 'Interviews' as Stage, interviews as Value from @torder by YearN[/code]EDIT: GOT DISTRACTED DID NOT SEE PREVIOUS POST*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
mparter
Yak Posting Veteran
86 Posts |
Posted - 2004-11-23 : 10:56:39
|
Thnaks for taking the time to reply, unfortunately, my headache caused me to post the wrong source layout  The source layout is infact:-Year Team Apps Int Cond Uncond Acc With Waiting---------------------------------------------------------------------------------2003/4 Team1 267 110 15 199 173 69 02003/4 Team2 22 1 48 43 10 0 02003/4 Team3 229 114 23 171 136 63 12004/5 Team1 808 424 24 441 365 284 402004/5 Team2 78 36 5 54 48 22 02004/5 Team3 181 96 2 131 121 41 02004/5 Team4 49 32 0 44 44 4 0 But I'm still looking for the same output. |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-11-23 : 11:01:16
|
quote:
Originally posted by mparter
But I'm still looking for the same output.
Same logic then *.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
mparter
Yak Posting Veteran
86 Posts |
Posted - 2004-11-23 : 11:12:00
|
The problem with that is that it gives me the following:-Year Stage Value----------------------------------2003/4 Applications 02003/4 Applications 12003/4 Applications 492003/4 Applications 592003/4 Applications 782003/4 Applications 1182003/4 Applications 1562003/4 Applications 1572003/4 Applications 1812003/4 Applications 2292003/4 Applications 2532003/4 Applications 2622003/4 Applications 2672003/4 Applications 2852003/4 Applications 3012003/4 Applications 4362003/4 Applications 8082003/4 Interviews 02003/4 Interviews 12003/4 Interviews 222003/4 Interviews 322003/4 Interviews 362003/4 Interviews 552003/4 Interviews 562003/4 Interviews 582003/4 Interviews 962003/4 Interviews 1032003/4 Interviews 1102003/4 Interviews 1142003/4 Interviews 1172003/4 Interviews 1342003/4 Interviews 1552003/4 Interviews 1582003/4 Interviews 4242004/5 Applications 02004/5 Applications 12004/5 Applications 582004/5 Applications 1002004/5 Applications 1402004/5 Applications 1592004/5 Applications 1712004/5 Applications 1832004/5 Applications 2042004/5 Applications 2352004/5 Applications 2612004/5 Applications 2722004/5 Applications 2762004/5 Applications 2802004/5 Applications 2872004/5 Applications 5242004/5 Applications 7172004/5 Interviews 02004/5 Interviews 362004/5 Interviews 542004/5 Interviews 832004/5 Interviews 942004/5 Interviews 992004/5 Interviews 1132004/5 Interviews 1192004/5 Interviews 1252004/5 Interviews 1582004/5 Interviews 1662004/5 Interviews 1682004/5 Interviews 1842004/5 Interviews 2902004/5 Interviews 381 What I'd like is:-Year Stage Value----------------------------------2003/4 Applications 36402003/4 Interviews 16712004/5 Applications 38692004/5 Interviews 2183 Sorry about the length of the post. And thanks :) |
|
|
|
mparter
Yak Posting Veteran
86 Posts |
Posted - 2004-11-23 : 11:14:46
|
I think I just worked it out myself Select acyear, 'Applications' as Stage, SUM(Applications) as Value from dbo.tbl_RS_Applicationsgroup by acyearUnionSelect acyear, 'Interviews' as Stage, SUM(Interviews) as Value from dbo.tbl_RS_Applicationsgroup by acyear_ayrc Thanks for all the help guys.As a side issue, I only posted a couple of columns here but I'll need to 'UNION' 7 all together, is this going to be slow? |
|
|
|
|
|
|
|
|
My head hurts!