Translate PIVOT query into SQL 2000 compatible?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Translate PIVOT query into SQL 2000 compatible??

Author

Topic

kunal333
Starting Member

1 Post

Posted - 2008-08-19 : 21:38:11

Need help in translating the below SQL Server 2005 code into SQL Server 2000.

Basically I need to re-write the below query without PIVOT, so that I can run in SQL Server 2000.

SELECT
UP.RecordID,
UP.StopNumber,
CASE StopNumberType
WHEN 'OrigTerminalStopNumber' THEN 'OT'
WHEN 'OrigRampStopNumber' THEN 'OR'
WHEN 'LoadingPortStopNumber' THEN 'OP'
WHEN 'DischargePortStopNumber' THEN 'DP'
WHEN 'DestRampStopNumber' THEN 'DR'
WHEN 'DestTerminalStopNumber' THEN 'DT'
END AS LocationType
FROM
(
SELECT
IL.RecordID,
IL.DestRampStopNumber,
IL.DestTerminalStopNumber,
IL.DischargePortStopNumber,
IL.LoadingPortStopNumber,
IL.OrigRampStopNumber,
IL.OrigTerminalStopNumber
FROM Table1 AS IL
) AS P

UNPIVOT
(
StopNumber FOR StopNumberType IN
(
P.DestRampStopNumber,
P.DestTerminalStopNumber,
P.DischargePortStopNumber,
P.LoadingPortStopNumber,
P.OrigRampStopNumber,
P.OrigTerminalStopNumber
)
) AS UP

Any help will be appreciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-19 : 23:33:09

use UNION
[code]
select . . . .
from table
union all
select . . . .
from table
[code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources