a4apple (9/11/2012)
Hi, I need to get status names as columns and their respective dates as rows. I know its pivoting, but asking you guys for more insight.Thanks
I think you need to do a little more than that, namely fully understand the data. For example, your expected output includes a column for 'Created_date', yet you do not provide a status of 'created'. Are you going to make an inference on this value based on it's existing in a table somewhere? Also, what happens if a user_id changes? Anyhow, this will give you your desired output, with the exception of the created_date, as I'll leave that for you to decide how you want to handle. I can think of about 10 ways this will fail depending on your real data, but it should be at least enough to get you started.
WITH Pvt AS
(
SELECT
issue_id,[created] ,[assign],[Inprogress],[Complete]
FROM
(
SELECT
a.issue_id,
a.created_dt,
f.name AS status_new
FROM jrn a INNER JOIN details b
ON a.id = b.jrn_id
CROSS APPLY
(
SELECT name FROM status f
WHERE id = b.new AND b.prop_key = 'status_id'
) f
) t1
PIVOT
(
MIN(created_dt) FOR status_new IN ([created],[assign],[Inprogress],[Complete])
) AS pvt
)
SELECT
a.issue_id,
c.name AS [Status],
a.created AS Created_dt,
a.assign AS Assign_dt,
a.inprogress AS Inprogress_dt,
a.Complete AS Complete_dt,
b.
FROM PVT a LEFT OUTER JOIN
(
SELECT
a.issue_id,
c.name AS
FROM jrn a INNER JOIN details b
ON b.jrn_id = a.id INNER JOIN users c
ON b.new = c.id
WHERE b.prop_key = 'user_id'
) b
ON a.issue_id = b.issue_id LEFT OUTER JOIN
(
SELECT
a.ISSUE_ID,
b.name
FROM
(
SELECT
a.issue_id,
MAX(b.new) AS status_id
FROM jrn a INNER JOIN details b
ON b.jrn_id = a.id
GROUP BY a.issue_id
) a INNER JOIN status b
ON a.status_id = b.id
) c
ON a.issue_id = c.issue_id
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.