Transpose multiple columns into one row mysql

Question

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.

You're looking for what in SQL Server is the `UNPIVOT` function. Unfortunately MySQL does have this capability out of the box, and I've been told that it gets very ugly, very fast. For the simple example you gave, you could just `UNION` together the values. — Tim Biegeleisen, Aug 17 '16 at 10:19

score 0 · Accepted Answer · edited May 23 '17 at 12:00

0

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;

edited May 23 '17 at 12:00

Community

1
1

answered Aug 17 '16 at 10:22

1000111

13,169
2
28
37

Thank you, it's works perfectly. But how i stock the results in a variable to use further ? – BOB Aug 17 '16 at 11:25

score 0 · Answer 2 · answered Aug 17 '16 at 11:30

You are almost there with your variable assignments. Just put union all between them:

select 't1', sum(t1) FROM tab_1 WHERE t1 > 0
union all
select 't2', sum(t2) FROM tab_1 WHERE t2 > 0
union all
select 't3', sum(t3) FROM tab_1 WHERE t3 > 0;

(And I removed the variable assignment, because that doesn't seem necessary.)

I would include an identifier so I know which value corresponds to which column.

Transpose multiple columns into one row mysql

2 Answers2