Need help with SELECT statement - one to mult rows

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

General SQL Server Forums

New to SQL Server Programming

Need help with SELECT statement - one to mult rows

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2010-06-03 : 06:29:17

I have to read a number of columns and reformat the data into multiple rows.

Here's how the data lives in the DB:
Name1 Addr1 City1 State1 Zip1 Name2 Addr2 City2 State2 Zip2 Name3 Addr3 City3 State3 Zip3

I need to retrieve the data in multiple rows like this:
Name1 Addr1 City1 State1 Zip1
Name2 Addr2 City2 State2 Zip2
Name3 Addr3 City3 State3 Zip3

I have to do this via a SELECT statement, no stored procedures or anything else like that.

Help, I am a novice at this.

thanks.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-03 : 06:39:48

SELECT ColName1, ColAddr1, ColCity1, ColState1, ColZip1 From Table
UNION ALL
SELECT ColName2, ColAddr2, ColCity2, ColState2, ColZip2 From Table
UNION ALL
SELECT ColName3, ColAddr3, ColCity3, ColState3, ColZip3 From Table
Order by <whatever you want>

I dont know how this table is designed and who has designed very badly.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

sbt1
Yak Posting Veteran

89 Posts

Posted - 2010-06-03 : 07:04:35

Yes, I know about the table design. Unfortunately I can't change it.

Thanks so much Vaibhav, it worked like a charm!!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-03 : 07:06:00

Welcome

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources