SQL create separate columns based on values of an existing column-CodePudding

I have a table looking like this with the following query in SQL

SELECT UpdateDate as DATE , 
Username ,
Request as Type ,
AmountUSD as Amount 
FROM tgm.tr_active 
where Request in ('Successfull','Declined');

And i would like to run a query and get the following results creating separate columns for eache type with the appropriate values , otherwise the value will be 0 like this :

Is there a way to do that?

CodePudding user response：

Use IF

SELECT
  Date,
  Username,
  IF (Type = 'Successful', Amount, 0) AS Successful,
  IF (Type = 'Declined', Amount, 0) AS Declined 
FROM table_1

CodePudding user response：

You can try the following query for your expected output.

SELECT  UpdateDate as DATE,
Username,
    COUNT(
        CASE 
            WHEN Type='Successfull' 
            THEN Amount 
            ELSE 0 
        END
    ) AS 'Successfull',
    COUNT(
        CASE 
            WHEN Type='Declined'
            THEN Amount
            ELSE 0 
        END
    ) AS 'Declined'
FROM    tr_active
WHERE  Request in ('Successfull','Declined');

I hope it will help you.