formatting data in sql to a single row-CodePudding

I have a sql which is returning the data like below:

SELECT DISTINCT
workitem_id,
case when state ='Created' then newimage_lastupdateddate else null end  as creation_date,
case when state ='Disconnected' then newimage_lastupdateddate else null end as disconnect_date
from work_item

workitem_id	channel	creation_date	diconnect_date
123	task	2021-10-02 08:37:24	null
123	task	null	2021-10-02 11:36:58

I want the data in 1 row as given below.

workitem_id	channel	creation_date	diconnect_date
123	task	2021-10-02 08:37:24	2021-10-02 11:36:58

How can I do it ?

CodePudding user response：

The DISTINCT is generating the issue.

Try

SELECT D
workitem_id,
min(case when state ='Created' then newimage_lastupdateddate else null end ) as creation_date,
max(case when state ='Disconnected' then newimage_lastupdateddate else null end) as disconnect_date
from work_item 
group by workitem_id

not sure if min and max are the optimal for your data. In case you are not sure , please copy the data here .

CodePudding user response：

Try aggregating and grouping by workitem_id

select workitem_id,
    Max(case when state ='Created' then newimage_lastupdateddate end) as creation_date,
    Max(case when state ='Disconnected' then newimage_lastupdateddate end) as disconnect_date
from work_item 
group by workitem_id