x

please help to convert rows to multiple columns in sql server 2000

hello,
i am trying to transform the results of the following query:

SELECT DISTINCT 
CASEID,
RCSTATUS as CASESTATUS,  
COALESCE(DATEDIFF(DAY, (
  SELECT MAX(ROWLASTMANTDTTM)   
  FROM PSRCACTION_HIST 
  WHERE ROWLASTMANTDTTM < a.ROWLASTMANTDTTM 
  AND CASEID = a.CASEID ),   a.ROWLASTMANTDTTM), 0) AS DAYSINSTATUS  
FROM PSRCACTIONHIST a


CASEID            CASESTATUS DAYSINSTATUS 
----------------- ----------- ------------- 
334082            CCUST       0  
334082            CUST        2  
334082            ENG         0  
334082            ENG         1  
334082            ENG         4  
334082            OPEN        0  
334082            RESOL       4  

i would like to have this result instead:

caseid status1 days status2 days status3 days
----------------- ----------- ------------- 
334082  CCUST  0     CUST 2  ENG 1 etc.
is it possible? i can not use pivot cause i have version 2000 of sql server thanks in advance
more ▼

asked Jul 28 '10 at 12:56 AM in Default

nat gravatar image

nat
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

See [Oleg's answer here][1] - this shows how to make a 2000 compatible cross-tab.

[1]: http://ask.sqlservercentral.com/questions/16296/how-do-you-move-data-from-a-record-into-a-new-columns/16300
more ▼

answered Jul 28 '10 at 02:37 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x76

asked: Jul 28 '10 at 12:56 AM

Seen: 985 times

Last Updated: Jul 28 '10 at 01:04 AM