SQL: Query formatting

Question

I have a SQL query with three columns. The first is Year (categorical), the second is Site (categorical) and the last is temperature (float). The rows as unique combinations of Year X Site. For example:

Current query result

Year, Site, Temp
1,    1,  x11
1,    2,  x12
1,    3,  x13
2,    1,  x21
2,    2,  x22
2,    3,  x23
3,    1,  x31
3,    2,  x32
3,    3,  x33

I would like to have each site as a different column, while keeping years as rows. For example:

Desired query result

Year, TSite1, TSite2, TSite3
 1,    x11,    x12,    x13
 2,    x21,    x22,    x23
 3,    x31,    x23,    x33

Any ideas on how to do a query that results in this format? I would not mind using a temporary table or a view to store the information.

Thanks in advance.

What DBMS are you using? Do you know all the sites beforehand? — Mosty Mostacho, Oct 29 '13 at 22:21
asked and answered many times... hhttp://stackoverflow.com/search?q=turn+rows+into+columns — Daniel E., Oct 29 '13 at 22:23
Then check this [answer](http://stackoverflow.com/questions/19525158/optimising-sql-queries/19527367#19527367). I think it is pretty clear — Mosty Mostacho, Oct 29 '13 at 22:29
Mosty, thanks for pointing me to the answer. The MAX() function did the trick. I had tried before and did not work without it... Thanks a lot! — Thiago Rangel, Oct 29 '13 at 22:46
Mosty, any ideas how I would do the same if list of sites was not known, or extracted from a query? — Thiago Rangel, Oct 29 '13 at 23:03

score 1 · Answer 1 · answered Oct 29 '13 at 22:33

1

SELECT Year,MIN(CASE WHEN Site=1 THEN Temp ELSE 0 END) as Tsite1,
        MIN(CASE WHEN Site=2 THEN Temp  ELSE 0 END) as Tsite2,
        MIN(CASE WHEN Site=3 THEN Temp  ELSE 0 END) as Tsite3 FROM table GROUP BY Year

answered Oct 29 '13 at 22:33

Mihai

26,325
7
66
81

Thanks a lot for the input. The trick is the MIN() [alternatively also MAX()] function, without which would not work. Best! – Thiago Rangel Oct 29 '13 at 22:47
Mihai, any idea how I would do the same if the list of sites was not known, or extracted from a query? – Thiago Rangel Oct 29 '13 at 23:05
I doubt it can be done if sites are not known,but ask a new question with more details if you need it. – Mihai Oct 29 '13 at 23:11

score 0 · Accepted Answer · answered Oct 29 '13 at 22:34

A pivot query is one approach (as mentioned in the comments) If you just want a comma-delimited list of sites, then you can do that with group_concat().

select year, group_concat(temp separator ', ' order by site) as temps
from t
group by year;

I realize this may not be exactly what you want -- you lose the type information for temp by converting it to a string for example. But then again, it may be what you need if you just want to see the temps or export them to another tool.

SQL: Query formatting

2 Answers2