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 |
mpruett
Starting Member
1 Post |
Posted - 2005-07-22 : 16:47:25
|
I have a problem- I have data that looks something like this:ID#, lastname1, firstname1, lastname2, firstname2and I'd like output similar to:ID#1 lastname1, firstname1ID#1 lastname2, firstname2ID#2 lastname1, firstname1ID#2 lastname2, firstname2How can I do this without moving my data into new tables or using temp-tables?Thanks,Mark |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-07-22 : 16:53:43
|
With a UNION...SELECT ID, lastname1, firstname1 FROM myTableUNION ALLSELECT ID, lastname2, firstname2 FROM myTableORDER BY ID---------------------------EmeraldCityDomains.com |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-25 : 01:22:09
|
Small modification of Ajarn code(Select * from (SELECT ID, lastname1, firstname1 FROM myTableUNION ALLSELECT ID, lastname2, firstname2 FROM myTable ) TORDER BY ID MadhivananFailing to plan is Planning to fail |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-07-29 : 13:13:47
|
Madhivan, why the modification? The ORDER BY in my clause does not get applied until after the union is performed anyway. Is this a difference in versions? Or just for clarity of code?---------------------------EmeraldCityDomains.com |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-01 : 00:59:23
|
>>Or just for clarity of code?YesIf he use Order by in First statement, he will get errorSELECT ID, lastname1, firstname1 FROM myTableORDER BY IDUNION ALLSELECT ID, lastname2, firstname2 FROM myTableMadhivananFailing to plan is Planning to fail |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-01 : 17:36:44
|
Yep! That's how I learned about it. ---------------------------EmeraldCityDomains.com |
|
|
|
|
|
|
|