Transposing Rows to Columns

Question

I am trying to come up with a single result set from two tables in a way that I have never done, and I am having a little bit of trouble figuring out how to do it or even what to search for in these forums. Consider the following hypothetical table data:

Table1
----------------------------------
ID  |  Name
----------------------------------
1   |  aa
2   |  bb
3   |  cc
4   |  dd
5   |  ee


Table2
----------------------------------
ID  |  Table1_ID  |  Value
----------------------------------
1   |  1          |  good
2   |  2          |  Dumb
3   |  3          |  Fat
4   |  4          |  Wet
5   |  5          |  High
6   |  1          |  Thin
7   |  2          |  Tall
8   |  3          |  Goofy
9   |  4          |  Rich
10  |  5          |  Funny

I am looking for a query or method that allows me to end up with the following result set: Code:

aa      |    bb     |    cc       |    dd      |    ee
---------------------------------------------------------------
good     |    Dumb   |    Fat     |    Wet     |    High
Thin     |    Tall   |    Goofy   |    Rich    |    Funny

Essentially, I want the ability to take the list of names from Table1, transpose them into column headers, then put all of Table2's values into their respective columns with the ability to sort on any column. Is this possible?

You can sort this out by saving your data logically into an array. Have you tried anything? — AyB, Mar 27 '14 at 12:13
I don't think this can be done in a raw SQL query itself. But in PHP code? sure... as I can Has Cheezburger said, if you first move everything into an array, you can modify it at will. — Tularis, Mar 27 '14 at 12:14

score 0 · Answer 1 · answered Mar 27 '14 at 12:19

Of course this can be done in SQL. But it is tricky. As the data is written in the question, you can group by t2.id in groups of 5. After that, the query is just conditional aggregation.

select max(case when t2.table1_Id = 1 then value end) as aa,
       max(case when t2.table1_Id = 2 then value end) as bb,
       max(case when t2.table1_Id = 3 then value end) as cc,
       max(case when t2.table1_Id = 4 then value end) as dd,
       max(case when t2.table1_Id = 5 then value end) as ee
from table2 t2 
group by cast(t2.id - 1 / 5 as int);

Having values be implicitly related by their ids seems like a really, really bad database design. There should be some sort of entity id that combines them.

Thank you for responding but the table1 id is dynamically changed...? — khan, Mar 27 '14 at 12:28
SQL queries have constant column names. If you want variable column names, you will have to use dynamic SQL. — Gordon Linoff, Mar 27 '14 at 13:57

score 0 · Answer 2 · edited May 23 '17 at 12:20

You've got two problems here: 1) Using values as column names can't be done in a clean way 2) You want to split table2.value in 2 rows: Which of the values should be on which row? Gordon Linoff uses the table2.id field for this, but if it's auto increment and your data gets some adds/deletes later on that rhythm will get broken.

There's been similar questions before. This one has an answer that gets pretty close:

mysql select dynamic row values as column names, another column as value

Here they generate the string for the query and make a prepared statement out of it.

Transposing Rows to Columns

2 Answers2