I have an SQL
query that selects data based on uploaded and not uploaded
id labname uploadstatus 322 CS Minhewene 0 323 CS Nacuale 0 324 CS Mesa 0 325 CS Metoro 0 326 CS Ngewe 0 327 CS Mariri 0 328 CS Ntutupue 0 329 CS Ngura 0 330 CS Ancuabe 1
this is the query am using to achieve the data above
SELECT l.id, l.facility_name as labName, CASE WHEN u.upload_status is NULL THEN '0' WHEN u.upload_status = '1' THEN '1' END As upload_status FROM labs l LEFT JOIN upload u ON u.lab_name = l.facility_name INNER JOIN districts d ON d.id = l.district_code
I want to go a step further and group the data into two columns. I want to get the labnames grouped using the uploadstatus that is 0 as Not Uploaded and the ones having 1 as Uploaded and represent it in two columns.
This what I am trying to achieve from the drilldown
Not Uploaded Uploaded CS Minhewene CS Ancuabe CS Nacuale CS Mesa CS Metoro CS Ngewe CS Mariri CS Ntutupue CS Ngura
How can I group the data using uploadstatus column and group them in new columns and separate the ones with the value of 0 to one column and the others with value of 1 to another column? Any help will be appreciated
Advertisement
Answer
SELECT l.id, l.facility_name AS labName, CASE WHEN u.upload_status IS NULL THEN l.facility_name ELSE '' END AS "Not Uploaded", CASE WHEN u.upload_status = '1' THEN l.facility_name ELSE '' END AS Uploaded FROM labs l LEFT JOIN upload u ON u.lab_name = l.facility_name INNER JOIN districts d ON d.id = l.district_code