I have a table1 as below

I want the output like

I have written the below query but it's showing either update data or insert data but not both.

select colA,
CASE WHEN update ="Yes" THEN "update"
WHEN insert ="Yes" THEN "insert"
end as check from Table1;

Please help me in achieving the above output as expected, Thanks in advance!

CodePudding user response：

I think you need UNION ALL:

SELECT ColA, 'Update' Check, ColB
FROM Table1
WHERE `Update` = 'Yes'
UNION ALL
SELECT ColA, 'Insert' Check, ColB
FROM Table1
WHERE `Insert` = 'Yes';

CodePudding user response：

CASE is not going to produce two rows where you only have one. Either use a union, or join an ad hoc table to check separately for updates and inserts:

select colA, check, colB
from (
    select 'update' as check union all select 'insert'
) check
join Table1 on
    case check
        when 'update' then Update='Yes'
        when 'insert' then Insert='Yes'
    end

This essentially loops twice over table1, once looking for updates and once for inserts.