英文:
Transpose or Pivot without using PIVOT or UNPIVOT SQL Editor in Metabase
问题
我正在尝试转置我的表格列和行，但是转置功能还没有包含在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
得分: 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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论