Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Unanswered: Translate PIVOT query into SQL Server 2000 compatible query??

    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!

  2. #2
    Join Date
    Apr 2007
    Posts
    183

    I hope you know the difference between PIVOt and UNPPIVOT !

    Code:
    SELECT	RecordID,
    	OrigTerminalStopNumber AS StopNumber,
    	'OT' AS LocationType
    FROM	Table1
    
    UNION ALL
    
    SELECT	RecordID,
    	OrigRampStopNumber,
    	'OR'
    FROM	Table1
    
    UNION ALL
    
    SELECT	RecordID,
    	LoadingPortStopNumber,
    	'OP'
    FROM	Table1
    
    UNION ALL
    
    SELECT	RecordID,
    	DischargePortStopNumber,
    	'DP'
    FROM	Table1
    
    UNION ALL
    
    SELECT	RecordID,
    	'DR',
    	DestRampStopNumber
    FROM	Table1
    
    UNION ALL
    
    SELECT	RecordID,
    	'DT',
    	DestTerminalStopNumber
    FROM	Table1

  3. #3
    Join Date
    Aug 2008
    Posts
    3

    PIVOT query??

    Thanks Peso. I got the idea!

    Folks, Any clue how can I re-write the below query without PIVOT this time.....

    SELECT
    P.RecordID,
    coalesce(MIN(ISNULL([Enter Date], 99990101)),99990101) AS EnterDate,
    coalesce(MAX(ISNULL([Close Date], 99990101)),99990101)AS CloseDate
    FROM
    (
    SELECT
    RecordID,
    DateType,
    MIN(ISNULL(D.DateValue, 99990101)) AS DateValue
    FROM #Table1 AS R
    INNER JOIN #Table2 AS T ON R.COID = T.COID
    GROUP BY R.RecordID, T.DateType
    ) AS UP(RecordID, DateType, DateValue)
    PIVOT
    (
    MIN(DateValue) FOR DateType IN ([Enter Date], [Close Date])
    ) AS P
    INNER JOIN [#Table1] AS R2 ON P.RecordID = R2.RecordID
    INNER JOIN [#Table3] AS R3 ON R2.CoID = R3.CoID
    GROUP BY P.RecordID

  4. #4
    Join Date
    Apr 2007
    Posts
    183

    What kind of queries do you post? This last one don't even pass syntax check.

    Code:
    SELECT		r1.RecordID,
    		MIN(CASE WHEN r2.DateType = 'Enter Date' THEN {r1 | r2 | r3}.DateValue ELSE 99990101 END) AS EnterDate,
    		MAX(CASE WHEN r2.DateType = 'Close Date' THEN {r1 | r2 | r3}.DateValue ELSE 10010101 END) AS CloseDate
    FROM		#Table1 AS r1
    INNER JOIN	#Table2 AS r2 ON r2.CoID = r1.CoID
    INNER JOIN	#Table3 AS r3 ON r3.CoID = r1.CoID
    GROUP BY	r1.RecordID
    ORDER BY	r1.RecordID

  5. #5
    Join Date
    Aug 2008
    Posts
    3
    Thanks again Peso for the quick reply!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •