How to write sql query to get this result

Question

I had two tables as below:

request_id	update_from_id	sw_ref_number	raised_by_user_id	raised_date
1	0	1	3	2019-08-29 15:08:16.000

id	request_id	input_id	value	is_deleted
21	1	1	00001	0
22	1	2	3	0
75	2	1	00002	0
76	2	2		0

My query is:

select req.request_id,
       req.sw_ref_number,
       reqDet.[value] , 
       reqDet.input_id 
FROM   SOF.tblSOFRequest req  
       left join SOF.tblSOFRequestDetails reqDet 
              on req.request_id = reqDet.request_id  
where  reqDet.input_id = 1 
or     reqDet.input_id = 2

And after that my result is:

request_id	sw_ref_number	value	input_id
1	1	00001	1
1	1	3	2
2	2	00002	1
2	2		2

I want a result as:

request_id	sw_ref_number	epi_db_no	manuf_no
1	1	00001	3
2	2	00002

Here input_id = 1 means epi_db_no and input_id = 2 means manuf_no.

How can I get this?

Thanks

Answer 1

You can do it using self-join like the following query.

SELECT req.request_id
    ,req.sw_ref_number
    ,reqDet.[value] AS epi_db_no
    ,reqDet2.[value] AS manuf_no
FROM SOF.tblSOFRequest req
LEFT JOIN SOF.tblSOFRequestDetails reqDet ON req.request_id = reqDet.request_id
LEFT JOIN SOF.tblSOFRequestDetails reqDet2 ON reqDet.request_id = reqDet2.request_id
    AND reqDet2.input_id = 2
WHERE reqDet.input_id = 1

Answer 2

This seems like a simple pivot. I use conditional aggregation here:

SELECT req.request_id,
       req.sw_ref_number,
       MAX(CASE reqDet.input_id WHEN 1 THEN reqDet.[value] END) AS epi_db_no,
       MAX(CASE reqDet.input_id WHEN 2 THEN reqDet.[value] END) AS manuf_no
FROM SOF.tblSOFRequest req 
     JOIN SOF.tblSOFRequestDetails reqDet ON req.request_id = reqDet.request_id
WHERE reqDet.input_id IN (1,2)
GROUP BY req.request_id,
         req.sw_ref_number;