How to Use Queries to show records based on conditions-CodePudding

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

CodePudding user response：

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