x
login about faq Site discussion (meta-askssc)

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

more ▼

answered Jul 28 '10 at 02:37 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.2k 56 63 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.

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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x913
x65

asked: Jul 28 '10 at 12:56 AM

Seen: 718 times

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

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.