英文:

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

问题 {#heading}

我正在尝试转置我的表格列和行，但是转置功能还没有包含在Metabase的功能中。现在我正在尝试在其SQL编辑器中手动执行，但是由于某种原因，PIVOT不是已知的功能。

我有一个包含列Interval、Count和Percentage的表格

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

我想将它转换成这样

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

我尝试过以下作为一种尝试。

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

但是生成的查询结果总是将百分比放在顶部，即使我更改select的顺序也是如此。

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

英文:

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 |

答案1 {#1}

得分: 0

使用以下代码：

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;

祝好运！ 英文:

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;