postgresql - 转置 Postgres 查询

Question

我正在查询，我想转置：

SELECT
    t.name,
    COUNT(*) qtd
FROM 
    user_video_audience uv, video v, tag t
WHERE
    uv.video_id = v.id AND
    uv.watched = 'true' AND
    v.tag_id = t.id
AND 
    uv.created_date >= (NOW() - INTERVAL '48 hours' )
GROUP by
    t.name
ORDER BY
    qtd desc

结果是这样的

name       |     qtd
---------------------
Skate      |     45    
Surf       |     52
Snowboard  |     64

我想像这样转置

Skate   |   Surf   |   Snowboard
---------------------------------    
  45    |    52    |     64

我能够以某种方式进行查询；

select
    sum(CASE WHEN name = 'Surfe' THEN qtd END) AS Surfe,
    sum(CASE WHEN name = 'Skate' THEN qtd END) AS Skate,
    sum(CASE WHEN name = 'Kitesurf' THEN qtd END) AS Kitesurf,
    sum(CASE WHEN name = 'Bike' THEN qtd END) AS Bike,
    sum(CASE WHEN name = 'Natureza' THEN qtd END) AS Natureza,
    sum(CASE WHEN name = 'Skimboard' THEN qtd END) AS Skimboard,
    sum(CASE WHEN name = 'Skydive' THEN qtd END) AS Skydive,
    sum(CASE WHEN name = 'Fotografia' THEN qtd END) AS Fotografia,
    sum(CASE WHEN name = 'Snowboard' THEN qtd END) AS Snowboard,
    sum(CASE WHEN name = 'Mergulho' THEN qtd END) AS Mergulho
from (
SELECT
    t.name,
    COUNT(*) qtd
FROM 
    user_video_audience uv, video v, tag t
WHERE
    uv.video_id = v.id AND
    uv.watched = 'true' AND
    v.tag_id = t.id
AND 
    uv.created_date >= (NOW() - INTERVAL '48 hours' )
GROUP by
    t.name
ORDER BY
    qtd desc) s

但问题是我不知道所有可以出现的运动。由于它是过去 24 小时的查询，因此该值始终会更改。

有什么建议吗？

谢谢！

标签： postgresql