PostgreSQL and columns to rows

Question

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?

please provide sample data and table structure. In the question you have data for same city, year and sex — Abhishek Ginani, Nov 13 '15 at 16:07
check this : http://stackoverflow.com/questions/33531728/postgresql-query-with-generated-columns/33547854#33547854 — Abhishek Ginani, Nov 13 '15 at 16:20

score 4 · Accepted Answer · edited May 23 '17 at 12:23

4

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;

edited May 23 '17 at 12:23

Community

1
1

answered Nov 13 '15 at 12:33

LongBeard_Boldy

802
8
20

Isn't that example in your link the other way around? I mean, it says columns to rows sure but that's not the case, is it? – James Brown Nov 13 '15 at 13:06
it is in a way, but different for your case, you cud use the idea to generate query escaping hell to type and copy past columns – LongBeard_Boldy Nov 13 '15 at 13:10

PostgreSQL and columns to rows

1 Answers1