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
– Abhishek, Commented Nov 13, 2015 at 16:07

Community · Accepted Answer · 2017-05-23 12:23:40Z

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, 2017 at 12:23

CommunityBot

11 silver badge

answered Nov 13, 2015 at 12:33

LongBeard_Boldy

9128 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Brown Over a year ago

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?

LongBeard_Boldy Over a year ago

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

Collectives™ on Stack Overflow

PostgreSQL and columns to rows

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related