mysql format results as columns not rows...

Question

Hi,

Using MySQL Server, I have a table structure like:

(Table test)

id Name

1 "test1"

2 "test2"

3 "test3"

When I perform the following query: "Select name from test", the results are:

"test1"

"test2"

"test3"

How can I adjust this query so the results are columns, as such:

"test1" "test2" "test3"

Answer 1

Why you would need the data to be returned like that from the database?

If you actually need the data in that format it would make more sense to loop through the results and append them to a string to get the data in the format you require, but this should be done using a programming language instead of directly from the database.

Answer 2

Generally speaking, it's better to format your output in the application layer. Imagine that your table has 728003 unique values in the 'name' column. Your result would be unusably wide.

However, if you really want to do this and you have some unifying column to group on, you could do:

SELECT unifying_column, GROUP_CONCAT(DISTINCT name) FROM test
GROUP BY unifying_column;

ps, group_concat can only hold a limited number of characters

---

Addendum

The only way I could thing to do it with multiple columns would be something like this (and it is a horrible kludge):

SELECT SUBSTRING_INDEX(nlist,',',1) AS col1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(nlist,',',2),',',-1) AS col2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(nlist,',',3),',',-1) AS col3,
       SUBSTRING_INDEX(SUBSTRING_INDEX(nlist,',',4),',',-1) AS col4,
       SUBSTRING_INDEX(SUBSTRING_INDEX(nlist,',',5),',',-1) AS col5,
       SUBSTRING_INDEX(SUBSTRING_INDEX(nlist,',',6),',',-1) AS col6,
       SUBSTRING_INDEX(SUBSTRING_INDEX(nlist,',',7),',',-1) AS col7,
       SUBSTRING_INDEX(SUBSTRING_INDEX(nlist,',',8),',',-1) AS col8,
       SUBSTRING_INDEX(SUBSTRING_INDEX(nlist,',',9),',',-1) AS col9,
       SUBSTRING_INDEX(nlist,',',-1) AS col10
FROM       
    (SELECT unifying_column, GROUP_CONCAT(DISTINCT name LIMIT 10) AS nlist FROM test
      GROUP BY unifying_column) AS subtable;

The above is not tested and may have syntax errors. I'm also not sure what it might do with the extra columns if there are fewer than 10 names.