Marius Marius - 3 months ago 25
MySQL Question

Transpose multiple columns into one row mysql

create table tab_1(
t1 decimal (10),
t2 decimal (10),
t3 decimal (10)
);

insert into tab_1(t1, t2, t3)
values(1,-2,-5);
insert into tab_1(t1, t2, t3)
values(-3,4,6);
insert into tab_1(t1, t2, t3)
values(5,1,2);

select @sum1:=sum(t1) FROM tab_1 WHERE t1 > 0
select @sum2:=sum(t2) FROM tab_1 WHERE t2 > 0
select @sum3:=sum(t3) FROM tab_1 WHERE t3 > 0

select @sum1, @sum2, @sum3;


Results:

@sum1 @sum2 @sum3
------------------
6 5 8


I have the query above and I want a query that transpose columns in one column and 3 rows, like below:

Sum
--
6
5
8


I use MySQL Workbench 6.3.7.

Answer

You can achieve this through UNION ALL

SELECT SUM(IF(t1 > 0 , t1, 0)) AS sum  FROM tab_1
UNION ALL
SELECT SUM(IF(t2 > 0 , t2, 0)) AS sum  FROM tab_1
UNION ALL
SELECT SUM(IF(t3 > 0 , t3, 0)) AS sum  FROM tab_1

UNION vs UNION ALL

EDIT:

In order to store the results in variables: confused.why do you need this?

SET @sum1 := 0;
SET @sum2 := 0;
SET @sum3 := 0;
SELECT @sum1 := SUM(IF(t1 > 0 , t1, 0)) AS sum   FROM tab_1
UNION ALL
SELECT @sum2:= SUM(IF(t2 > 0 , t2, 0)) AS sum FROM tab_1
UNION ALL
SELECT @sum3 := SUM(IF(t3 > 0 , t3, 0)) AS sum  FROM tab_1;

SELECT @sum1,@sum2,@sum3;