I have a table (in PostgreSQL 9.3) with amount of people in cities grouped by their ages like this:
city | year | sex | age_0 | age_1 | age_2 | ... | age_115
---------------------------------------------------------
city1| 2014 | M | 12313 | 23414 | 52345 | ... | 0
city1| 2014 | F | 34562 | 23456 | 53456 | ... | 6
city2| 2014 | M | 3 | 2 | 2 | ... | 99
I'd like to break the columns down to rows, ending up with rows like this:
city | year | sex | age | amount | age_group
--------------------------------------------
city1| 2014 | M | 0 | 12313 | 0-6
city1| 2014 | M | 1 | 23414 | 0-6
city1| 2014 | M | 2 | 52345 | 0-6
city1| 2014 | M | ... | ... | ...
city1| 2014 | M | 115 | 0 | 7-115
and so on. I know I could do it with several (a lot) queries and UNIONs but instead I was wondering if there was a more elegant (less cut'n paste involving) way of doing such a query?
use arrays and unnest
select city,
year,
sex,
unnest(array[age_0 , age_1 , age_2 , ..., age_115]) as amount,
unnest(array[ 0 , 1 , 2 , ... , 115]) as age
from mytable
on large datasets this might be slow
did a quick look, there are many similar questions already asked , one good one with a good guide to dynamically generate the query you need ... les pasting for you link
generate query idiea
SELECT 'SELECT city , year , sex , unnest(ARRAY[' || string_agg(quote_ident(attname) , ',') || ']) AS amount from mytable' AS sql
FROM pg_attribute
WHERE attrelid = 'mytable'::regclass and attname ~ 'age_'
AND attnum > 0
AND NOT attisdropped
GROUP BY attrelid;