SQL create separate columns based on values of an existing column

Question

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?

Please Google for "MySQL pivot query" or have a read of [this question](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql). — Tim Biegeleisen, Jun 22 '22 at 09:23

NikuNj Rathod · Answer 1 · 2022-06-22T11:27:11.097

2

You can try the following query for your expected output.

SELECT UpdateDate AS DATE,
       Username,
       CASE
           WHEN TYPE='Successfull' THEN Amount
           ELSE 0
       END AS 'Successfull',
       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.

edited Jun 22 '22 at 11:27

answered Jun 22 '22 at 09:29

NikuNj Rathod

1,663
1
17
26

1

Sorry for the late response , this actually helped thank you very much – George Papadopoulos Aug 22 '22 at 11:26

score 2 · Answer 2 · answered Jun 22 '22 at 09:34

2

Use IF

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

answered Jun 22 '22 at 09:34

dikesh

2,977
2
16
26

Sorry for the late response , this actually helped thank you very much – George Papadopoulos Aug 22 '22 at 11:26

SQL create separate columns based on values of an existing column

2 Answers2