Transpose or Pivot without using PIVOT or UNPIVOT SQL Editor in Metabase

Question

I am trying to transpose my table columns and rows however transpose is not yet included in the functionality of Metabase. Now I am trying to manually do it in its SQL Editor however PIVOT is not a known functionality for some reason.

I have a table with columns Interval, Count, Percentage

Interval	Count	Percentage
0 - 60	50	.78
61 - 120	10	.16
120 >	4	.06

And I am trying to convert it like this

0 - 60	61 - 120	120 >
50	10	4
.78	.16	.06

What I have tried is using as a trial.

select 
    max(case when Interval = '0 - 60' then Count end) as "0 - 60"
    max(case when Interval = '0 - 60' then Count end) as "0 - 60"
    max(case when Interval = '0 - 60' then Count end) as "0 - 60"
from t
union
select 
    max(case when Interval = '0 - 60' then Percentage end) as "0 - 60"
    max(case when Interval = '0 - 60' then Percentage end) as "0 - 60"
    max(case when Interval = '0 - 60' then Percentage end) as "0 - 60"
from t

But the resulting query will always put percentage at the top first even if I change the order of select

0 - 60	61 - 120	120 >
.78	.16	.06
50	10	4

I added order by "0 - 60" desc as a countermeasure however there will be possibilities of 0 - 60 count being 0 and 0 percentage so it might be inconsistent. — hasperglasses, Aug 10 '23 at 10:54

score 0 · Accepted Answer · answered Aug 10 '23 at 11:13

Use following Code

SELECT *
FROM
(
  SELECT
    max(CASE WHEN Interval = '0 - 60' THEN Percentage END) AS ' 0 - 60',
    max(CASE WHEN Interval = '61 - 120' THEN Percentage END) AS ' 61 - 120',
    max(CASE WHEN Interval = '120 >' THEN Percentage END) AS ' 120 >',
    2 as sort_order
  FROM #TESTS

  UNION 

  SELECT
    max(CASE WHEN Interval = '0 - 60' THEN Count END) AS ' 0 - 60',
    max(CASE WHEN Interval = '61 - 120' THEN Count END) AS ' 61 - 120',
    max(CASE WHEN Interval = '120 >' THEN Count END) AS ' 120 >',
    1 as sort_order
  FROM #TESTS
) t
ORDER BY sort_order ;

Good Luck;

Is there a way to "Dynamically" do this? Since the interval is already known but what if the intervals are different how do I still transpose this? — hasperglasses, Aug 16 '23 at 12:40

Transpose or Pivot without using PIVOT or UNPIVOT SQL Editor in Metabase

1 Answers1