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