Transforming a table for a view

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

Transact-SQL (2000)

Transforming a table for a view

Author

Topic

kinger
Starting Member

1 Post

Posted - 2007-08-01 : 14:17:43

I'm working with sql server 2000 and I don't have control over the table design

The table looks like this

ID city State
---------------------------
1 baltimore MD
1 houstan TX
1 Meridian MS
1 huntsville Al
1 erie PA

I need the view to look like this

ID city_1 State_1 city_2 state_2 city_3 state_3 --->
----------------------------------------------------------------
1 baltimore MD houstan TX meridian MS --->

There is never a case where the ID number is repeated more then 5 times.

Is this something that is possible?

If it is, how would I go about accomplishing this?

Thanks for your help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-02 : 01:26:06

Read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-08-02 : 02:11:57

there are several articles on cross tab or pivot tables here on sqlteam. if you go to the main page and do a search you will find them.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources