SQL Nested Group within the Same Table

Question

I have data that looks something like this:

id      key         value
--------------------------------
1       country     us
1       state       nj
1       city        trenton
2       country     us
2       state       nj
2       city        springfield
3       country     us
3       state       nj
3       city        belmar
4       country     us
4       state       ny
4       city        new york
4       country     us
4       state       ny
4       city        white plains
4       country     canada
4       state       ontario
4       city        toronto
5       country     us
5       state       nj
5       city        trenton

I'm trying to write a SQL query to group by unique country, state, city

country     state       city
--------------------------------------
us          nj          trenton
us          nj          springfield
us          nj          belmar
us          ny          new york
us          ny          white plains
canada      ontario     toronto

I'm able to get DISTINCT country, state, and city, but I'm not sure how to join them together with the ID column.

There should be no need to 'group' them with the ID column, just `SELECT id, ...` and the rest of your query. — Obsidian Age, Sep 08 '19 at 23:25
Looks like a key value pair table. Pivot the data first then your good; but note you need to know the number of keys you are pivoting... https://stackoverflow.com/questions/126271/key-value-pairs-in-relational-database — xQbert, Sep 08 '19 at 23:53

score 1 · Accepted Answer · answered Sep 08 '19 at 23:31

1

It's worth stating for the record that this is a data mess and begs for "normalization". Having said that, you might try:

    SELECT cn.value as country, st.value as state, ct.value as city
    FROM table cn
    LEFT JOIN table st ON cn.id = st.id
    LEFT JOIN table st ON cn.id = ct.id
    WHERE cn.key = 'country'
    AND st.key = 'state'
    AND ct.key = 'city'
    ORDER BY cn.value , st.value , ct.value

answered Sep 08 '19 at 23:31

daShier

2,056
2
8
14

thank you! yeah.. the data ain't pretty. it's the wordpress post meta table. – YarGnawh Sep 08 '19 at 23:37

score 1 · Answer 2 · answered Sep 09 '19 at 00:23

You can use conditional aggregation for this:

select t.id,
       max(case when t.key = 'country' then t.value end) as country,
       max(case when t.key = 'state' then t.value end) as state,
       max(case when t.key = 'city' then t.value end) as city
from t
group by id;

This has the advantage of keeping all ids, even when not all three values are populated.

SQL Nested Group within the Same Table

2 Answers2