| Author |
Topic |
|
Crespo24
Village Idiot
144 Posts |
 Posted - 2003-03-17 : 06:22:49
|
Hi there,so I have the following table structure...KEY.........AMOUNT......CODE34..........246296......Pre97 Excess34..........65100.......Post 88 GMP34..........72406.......Post 97 Excess36..........NULL........Pre97 Excess36..........18504.......Post 88 GMP63..........45595.......Pre97 Excess63..........35009.......Post 97 Excesswhat I want to do is format the above table and have it so that the data for KEY = 34 are ALL one line..Something like:KEY Pre97 Excess Amount, Post88 GMP, Amount etc etc...I know you're probably thinking (transpose) etc.. but how on earth do you do it.Please help. |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-17 : 06:47:09
|
Converting Multiple Rows into a CSV String (Set Based Method) oops...I misread the question...try something like...select key, max(case when code = 'Pre97 Excess' then amount else null end), max(case when code = 'Post88 GMP' then amount else null end)from ....where key = 34group by key Jay White{0}Edited by - Page47 on 03/17/2003 08:17:47 |
 |
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-03-17 : 07:51:20
|
quote:
Converting Multiple Rows into a CSV String (Set Based Method)Jay White{0}
Thanks for that... even though....never mind. thanks for the link. I did a quick search here for the word transpose but nothing came up, and judging by the amount of response I got for my question it looks like a new topic might be handy for this.Has anyone written a helpful piece on transposing data?P.S.It is all your fault by the way (me getting the Child title)... I think you are loved too much here, well Damian loves you too much... #SIGH# |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-17 : 09:01:27
|
| Transposing is commonly refered to as "cross tabbing" .... search for "cross tab" that and you'll find some great stuff. Rob has a great stored proc that creates them dynamically.the method Jay showed you is my personal favorite when you know exactly which fields you are transforming into columns ... if you had numeric data and you wanted totals in each column, you would change the aggregate function from MAX() to SUM().- Jeff |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-03-17 : 09:09:27
|
quote:
Transposing is commonly refered to as "cross tabbing" .... search for "cross tab" that and you'll find some great stuff. Rob has a great stored proc that creates them dynamically.the method Jay showed you is my personal favorite when you know exactly which fields you are transforming into columns ... if you had numeric data and you wanted totals in each column, you would change the aggregate function from MAX() to SUM().- Jeff
Thanks for that... unfortunately... even though Jay's method is a sound one (although I have not had the chance to go through it properly yet).. I refuse to use it because it is his method. I am anti-Jay at the moment!  but it does work!  Yea.... I was surprised when the search returned nothing for the word transpose. Maybe it should be added to the topics as well. Just a suggestion to the SQL-team.Thanks for your help... much appreciated. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-17 : 13:32:00
|
| WHY anyone helps you is beyond me....Ignorance, thy name is Rio CrespoBrett8-) |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-03-18 : 05:19:06
|
quote:
WHY anyone helps you is beyond me....Ignorance, thy name is Rio CrespoBrett8-)
Suck your mum please... just go and suck your mum...Ignornace? What do you know about ignorance?You're American aren't you? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-03-18 : 08:33:07
|
Idiotquote:
It is all your fault by the way (me getting the Child title)...
You just don't get it do you ?Damian |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-18 : 08:38:58
|
quote:
Idiotquote:
It is all your fault by the way (me getting the Child title)...
You just don't get it do you ?Damian
Wait Wait Wait ... You mean you don't love me? What, is there someone else? And to think of all the sacrifices I've made to make this relationship successful ... musta just been my american ignorance ...Jay White{0} |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-03-18 : 08:43:47
|
| No it's not that. It's just that well, I have a commitment problem. It's not you, it's me.... I'm sorry. We can still be friends.Damian |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-18 : 09:20:21
|
| Maybe BABY would be a better titleBrett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-18 : 09:24:58
|
| I just Noticed the new title...very cool, AND Appropriate, much better than my suggestion.Brett8-) |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-03-18 : 11:58:37
|
quote:
No it's not that. It's just that well, I have a commitment problem. It's not you, it's me.... I'm sorry. We can still be friends.Damian
you can suck your mum too...I don't get it?! LOL...Do you have any idea how much of a laugh we've been having at work reading your oh-so-mature attitude bullshit?!Do you have any idea how much fun I get out of teasing you apathetic bunch of twats?Keep it comming..... I love it!See you later you pathetic fuckwit.... |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-03-18 : 11:59:44
|
quote:
I just Noticed the new title...very cool, AND Appropriate, much better than my suggestion.Brett8-)
TWO WORDS... KUS UMAAK! |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-03-18 : 13:16:17
|
quote:
Idiotquote:
It is all your fault by the way (me getting the Child title)...
You just don't get it do you ?Damian
You're a child inside too and not only that... you're a bully at heart.... and you know it.Let me tell you something about a little story oh so mature Damian...I was waiting for a taxi the other day with 3 other people. I was ahead of everyone when this black dude jumps in front and takes my cab. People around me were shocked!Now I had two choices....I could have REACTED and argued with him till dawn about itORI could have ACTED (which I did) and said nothing and waited a total of 30 seconds for the taxi behind to pull over.You see oh so wise Damian... it is ACTION vs REACTION....When I mentioned the custom title, YOU and the others REACTED in such a way that it actually surprised me.I felt quite insulted.... I remember the first time coming to this part of the website and I noticed your weird but light sense of humour and I joined in the fun with no bad intention intended.My enquiry was bombarded with nothing but insults.. and so I decided to play my little game on you guys.It's been fun and I hope it carries on because the more I do it the more your start to expose your true colours.....So the conclusion is :MATURE:ROBVOLKNRANDRAAXand a few more (can't remember their name)BASTARDSThat's you! and X002548 APATHETIC IDIOTSPage[40shite]and a few more Americans here who think OBL+Iraq are linked.Keep it coming Merkin....You know it's quite funny... EVERYTIME I think to myself that I should just calm it down a bit and stir up some shit later you end up showing your true colours even more and so I decide to carry on with my little game a bit longer.I might be an idiot because I come from a village... and hey I am a Child, so you expect a child to be an idiot sometimes...BUT you bunch of arrogant thugs have proved yourselves to be the REAL IDIOTS because you fell for this shit....Finally.. I've noticed something really funny about you Merkin... you usually respond to my messages in a controled and professional manner BUT then you come back all fired up... LOL... do you stand in front of the mirror at home wearing your grandmother's underware and see how hard you are? LOLAnyway.... KEEP YOUR POUDER DRY AND YOUR PECKER HARRRRRD.p.s.I;ve deicded, your my new BIATCH Merkin! |
|
|
|
|
Output problem...