Postgres columns to rows

Question

I have a PostgreSQL table which gives me the following output:

country	metric_code	male	female	pensioners	teenagers
us	metric_1	10	14	22	30
us	metric_2	105	142	222	309
uk	metric_1	15	13	9	21
uk	metric_2	212	264	440	165

create table tbl (
    region text,
    metric_code text,
    "male" int,
    "female" int,
    "pensioners" int,
    "teenagers" int
);

insert into tbl(region, metric_code, "male", "female", "pensioners", "teenagers")
    values
        ('us', 'metric_1', 10, 14, 22, 30),
        ('us', 'metric_2', 105,142,222,309),
        ('uk', 'metric_1', 15,13,9,21),
        ('uk', 'metric_2', 212,264,440,165);

Now I want the Output as below:

country	predicate_code	metric_1	metric_2
us	male	10	105
us	female	14	142
us	pensioners	22	222
us	teenagers	30	309
uk	male	15	212
uk	female	13	264
uk	pensioners	9	440
uk	teenagers	21	165

How to do this?

I wrote this query:

select region, predicate_code,
        max(case when metric_code = 'metric_1' then value end) as metric_1,
        max(case when metric_code = 'metric_2' then value end) as metric_2
    from tbl t
    cross join lateral (
        values
            ('male', "male"), 
            ('female', "female"),
            ('pensioners', "pensioners"),
            ('teenagers', "teenagers")
    ) as v(predicate_code, value)
    group by region, predicate_code
    order by 1, 2

are there any other ways? without group by / case?

Query you wrote looks OK to me. Other option(s) exist (e.g. UNION of 4 similar queries), but are *worse* than yours. — Littlefoot, Aug 27 '23 at 09:29
This is a excellent example of why pivoting/transposing in SQL is a very bad idea; SQL is very bad at it. Use SQL for data retrieval - it very good at it. Perform the pivot/transpose in your apps presentation manager - it is (should be) much better at it. — Belayer, Aug 28 '23 at 17:10

Amira Bedhiafi · Answer 1 · 2023-08-27T13:09:21.723

create table tbl (
    region text,
    metric_code text,
    "male" int,
    "female" int,
    "pensioners" int,
    "teenagers" int
);

insert into tbl(region, metric_code, "male", "female", "pensioners", "teenagers")
    values
        ('us', 'metric_1', 10, 14, 22, 30),
        ('us', 'metric_2', 105,142,222,309),
        ('uk', 'metric_1', 15,13,9,21),
        ('uk', 'metric_2', 212,264,440,165);

WITH metrics_m1 AS (
  SELECT region, 
         male AS male_metric_1, 
         female AS female_metric_1, 
         pensioners AS pensioners_metric_1, 
         teenagers AS teenagers_metric_1
  FROM tbl 
  WHERE metric_code = 'metric_1'
),

metrics_m2 AS (
  SELECT region, 
         male AS male_metric_2, 
         female AS female_metric_2, 
         pensioners AS pensioners_metric_2, 
         teenagers AS teenagers_metric_2
  FROM tbl 
  WHERE metric_code = 'metric_2'
)

SELECT 
  m1.region, 
  'male' AS predicate_code, 
  male_metric_1, 
  male_metric_2
FROM metrics_m1 m1
JOIN metrics_m2 m2 ON m1.region = m2.region

UNION ALL

SELECT 
  m1.region, 
  'female', 
  female_metric_1, 
  female_metric_2
FROM metrics_m1 m1
JOIN metrics_m2 m2 ON m1.region = m2.region

UNION ALL

SELECT 
  m1.region, 
  'pensioners', 
  pensioners_metric_1, 
  pensioners_metric_2
FROM metrics_m1 m1
JOIN metrics_m2 m2 ON m1.region = m2.region

UNION ALL

SELECT 
  m1.region, 
  'teenagers', 
  teenagers_metric_1, 
  teenagers_metric_2
FROM metrics_m1 m1
JOIN metrics_m2 m2 ON m1.region = m2.region

ORDER BY 1, 2;

region	predicate_code	male_metric_1	male_metric_2
uk	female	13	264
uk	male	15	212
uk	pensioners	9	440
uk	teenagers	21	165
us	female	14	142
us	male	10	105
us	pensioners	22	222
us	teenagers	30	309

fiddle

Update : Using dynamic transpose :

CREATE OR REPLACE FUNCTION dynamic_transpose()
RETURNS TABLE (
    country text,
    predicate_code text,
    metric_1 int,
    metric_2 int
) AS $$
DECLARE
    sql_query text;
BEGIN
    sql_query := 'SELECT t.region, x.predicate_code';

    -- For each metric code, add a column
    FOR r IN (SELECT DISTINCT metric_code FROM tbl ORDER BY metric_code) 
    LOOP
        sql_query := sql_query || ', MAX(CASE WHEN t.metric_code = ''' || r.metric_code || ''' THEN x.value ELSE NULL END) AS ' || r.metric_code;
    END LOOP;

    sql_query := sql_query || ' FROM tbl t CROSS JOIN LATERAL (VALUES 
        (''male'', "male"), 
        (''female'', "female"),
        (''pensioners'', "pensioners"),
        (''teenagers'', "teenagers")
    ) AS x(predicate_code, value) GROUP BY t.region, x.predicate_code ORDER BY t.region, x.predicate_code';

       RETURN QUERY EXECUTE sql_query;
    END;
    $$ LANGUAGE plpgsql;

    SELECT * FROM dynamic_transpose();

this solution is not suitable, because there can be 40 predicates and 10 metrics. the code becomes heavy and unreadable — Maximusrs, Aug 27 '23 at 12:28
Mmm let me think ! you are looking for other ways ? it is another way, so try to be more precise ;) — Amira Bedhiafi, Aug 27 '23 at 13:10

score 1 · Answer 2 · answered Aug 29 '23 at 22:11

you can try this

SELECT
region,
predicate_code,
MAX(value) FILTER (WHERE metric_code = 'metric_1') AS metric_1,
MAX(value) FILTER (WHERE metric_code = 'metric_2') AS metric_2
FROM (
SELECT region, metric_code, 'male' AS predicate_code, male AS value FROM 
tbl
UNION ALL
SELECT region, metric_code, 'female', female FROM tbl
UNION ALL
SELECT region, metric_code, 'pensioners', pensioners FROM tbl
UNION ALL
SELECT region, metric_code, 'teenagers', teenagers FROM tbl
) AS unpivoted
GROUP BY region, predicate_code
ORDER BY region, predicate_code;

score 0 · Answer 3 · answered Aug 27 '23 at 13:09

I would flip the table into jsonb and then use jsonb_each_text() to get the rows desired, using group by to combine metric_1 and metric_2 into single rows:

with cols_to_rows as (
  select region, metric_code, 
         to_jsonb(tbl) - 'region' - 'metric_code' as metrics
    from tbl
)
select cr.region as country, e.key as predicate_code, 
       max(e.value) filter (where metric_code = 'metric_1') as metric_1,
       max(e.value) filter (where metric_code = 'metric_2') as metric_2
  from cols_to_rows cr
       cross join lateral jsonb_each_text(cr.metrics) as e(key, value)
 group by cr.region, e.key
;

Working fiddle

score 0 · Answer 4 · answered Aug 27 '23 at 14:57

Try this out:

SELECT
    region,
    'male' AS predicate_code,
    "male" AS metric_1,
    NULL AS metric_2
FROM tbl
UNION ALL
SELECT
    region,
    'female' AS predicate_code,
    "female" AS metric_1,
    NULL AS metric_2
FROM tbl
UNION ALL
SELECT
    region,
    'pensioners' AS predicate_code,
    "pensioners" AS metric_1,
    NULL AS metric_2
FROM tbl
UNION ALL
SELECT
    region,
    'teenagers' AS predicate_code,
    "teenagers" AS metric_1,
    NULL AS metric_2
FROM tbl
UNION ALL
SELECT
    region,
    'male' AS predicate_code,
    NULL AS metric_1,
    "male" AS metric_2
FROM tbl
UNION ALL
SELECT
    region,
    'female' AS predicate_code,
    NULL AS metric_1,
    "female" AS metric_2
FROM tbl
UNION ALL
SELECT
    region,
    'pensioners' AS predicate_code,
    NULL AS metric_1,
    "pensioners" AS metric_2
FROM tbl
UNION ALL
SELECT
    region,
    'teenagers' AS predicate_code,
    NULL AS metric_1,
    "teenagers" AS metric_2
FROM tbl
ORDER BY region, predicate_code;

Postgres columns to rows

4 Answers4

Update : Using dynamic transpose :