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, 2010 at 12:56 AM in Default

avatar 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 - this shows how to make a 2000 compatible cross-tab.

more ▼

answered Jul 28, 2010 at 02:37 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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.

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:

x1065
x101

asked: Jul 28, 2010 at 12:56 AM

Seen: 1254 times

Last Updated: Jul 28, 2010 at 01:04 AM

Copyright 2016 Redgate Software. Privacy Policy