How do I convert the unique data of repetitive records into a new column

[scottholloway]

Current Sybase SQL Query
(Referance = http://manuals.sybase.com/onlinebook...g1100e/sqlref/)

[CODE=sql]SELECT
requisition.req n
requisition.pn
requisition.ord _time
status.status
status.status_t ime
requisition.del _time
DATEDIFF(HH, requisition.ord _time, requisition.del _time) 'HR'

FROM
requisition,
status

WHERE
requisition.req n *= status.reqn
AND DATEPART(year, requisition.del _time) = 2010
AND DATEPART(month, requisition.del _time) = 7[/CODE]

Current output:

REQN  PN ORD-TIME STATUS  STATUS-TIME DEL-TIME HR
------------------------------------------------------------
5A00 01201 10:00  Validating 10:00     19:46    9
5A00 01201 10:00  In Process 10:26     19:46    9
5A00 01201 10:00  Delivery   14:19     19:46    9
5A00 01201 10:00  Issued     10:00     19:46    9
5A00 01201 10:00  Completed  19:46     19:46    9
6R21 24582 02:45  Validating 02:45     07:57    5
6R21 24582 02:45  In Process 03:01     07:57    5
6R21 24582 02:45  Delivery   06:27     07:57    5
6R21 24582 02:45  Issued     07:02     07:57    5
6R21 24582 02:45  Completed  07:57     07:57    5

Ideal Output: There is only one record for each REQN and the STATUS becomes a header with the coresponding time as a field for that column/record.

REQN  PN ORD-TIME VALIDATING IN-PROCESS DELIVERY ISSUED COMPLETED DEL-TIME HR
---------------------------------------------------------------------------------
5A00 01201 10:00    10:00      10:26      14:19   10:00   19:46    19:46   9
6R21 24582 02:45    02:45      03:01      06:27   07:02   07:57    07:57   5

[Atli]

Hey.

I don't know much about Sybase, but if I needed to do this in MySQL or MSSQL, I would probably try using a view, creating each of the status columns based on subqueries.

For example:
[code=sql]CREATE VIEW req_status AS
SELECT
r.reqn,
r.pn,
r.ord_time,
r.del_time,
DATEDIFF(HH, requisition.ord _time, requisition.del _time) AS 'HR'
(
SELECT status_time FROM status
WHERE reqn = r.reqn
AND status = 'Validating'
) AS 'Validating',
(
SELECT status_time FROM status
WHERE reqn = r.reqn
AND status = 'In Process'
) AS 'In Process',
(
SELECT status_time FROM status
WHERE reqn = r.reqn
AND status = 'Delivery'
) AS 'Delivery',
(
SELECT status_time FROM status
WHERE reqn = r.reqn
AND status = 'Issued'
) AS 'Issued',
(
SELECT status_time FROM status
WHERE reqn = r.reqn
AND status = 'Completed'
) AS 'Completed'

FROM
requisition AS r,
status AS s

WHERE ...[/code]

[scottholloway]

Much thanks..

That is exactly what I was thinking but being new to SQL I was unsure of the syntax and if I could use a Select statement within a Select statement. I will test it at work on Monday and post a reply with the results. Regardless, thanks for the help.

[scottholloway]

It worked great. Thanks again.