Converting row to columns in mysql

Question

My table is coming from the output of a stored procedure. It looks like :

col1 
A 
B
C
D

It can have less than or equal to 5 rows.
I want it to look like this

Column1  Column2 Column3  Column4  column5 
A          B      C         D       NULL

Is there a way to achieve this in MYSQL?

Duplicated of: https://stackoverflow.com/questions/13581482/transpose-mysql-query-need-rows-into-columns — viniciusjssouza, May 28 '19 at 14:46
The problem is I don;t know how can I use IF statement here as there is only single column and it changes after every query. so i don't have anything to compare on IF statement — No_body, May 28 '19 at 14:48
Possible duplicate of [Transpose MySQL query - need rows into columns](https://stackoverflow.com/questions/13581482/transpose-mysql-query-need-rows-into-columns) — forpas, May 28 '19 at 14:51
Without a column which indicates a order you can't trustworthy convert these records into matching columns.. As SQL results/tables are defined to be **orderless** by standards unless `ORDER BY col1` is fine to use offcource — Raymond Nijland, May 28 '19 at 14:51
@RaymondNijland I honestly don't care about the order. All I want them to be in one row. one way that i am doing is like this SET var1 = ( SELECT listingid FROM final_table LIMIT 1); SET var2 = (SELECT listingid FROM final_table LIMIT 1,1); but i feel like this will be slower as if i have let's say 10 rows. — No_body, May 28 '19 at 14:55

score 2 · Answer 1 · answered May 28 '19 at 14:48

2

In MySQL 8+, there is a fairly easy way to do this using ROW_NUMBER along with a pivot query:

WITH cte AS (
    SELECT col1, ROW_NUMBER() OVER (ORDER BY col1) rn
    FROM yourTable
)

SELECT
    MAX(CASE WHEN rn = 1 THEN col1 END) AS Column1,
    MAX(CASE WHEN rn = 2 THEN col1 END) AS Column2,
    MAX(CASE WHEN rn = 3 THEN col1 END) AS Column3,
    MAX(CASE WHEN rn = 4 THEN col1 END) AS Column4,
    MAX(CASE WHEN rn = 5 THEN col1 END) AS Column5
FROM cte;

answered May 28 '19 at 14:48

Tim Biegeleisen

502,043
27
286
360

unfortunately, still struck on 5.1 :( – No_body May 28 '19 at 14:50
*"unfortunately, still struck on 5.1 "* i wished you where using MySQL 8 @No_body as you are most likely not going to like mine approach – Raymond Nijland May 28 '19 at 15:28

Raymond Nijland · Accepted Answer · 2019-05-28T15:42:38.117

Below MySQL 8.0 the approach becomes much more complex..

The simple explainment about the hard part the first query
The subquery within

  SELECT 
     GROUP_CONCAT(t.col1 ORDER BY col1 ASC) AS cvs
   , COUNT(*) AS t_count
  FROM 
   t

is making a comma separated values list. Then the SQL number generator in combination with nested SUBSTRING_INDEX() functions is splitting the comma separated values into records. Which is then merged into one string by GROUP_CONCAT() into the user variable basically iam generating dynamic SQL which lookes like '<value>' AS Column<number>[, ...] that is what SELECT @aggregateSQLPart; shows you

Query

SET @aggregateSQLPart = NULL; 

# set max of GROUP_CONCAT higher as it defaults to 1024 bytes. 
SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT 
 DISTINCT
   GROUP_CONCAT(CONCAT("'", 
    SUBSTRING_INDEX(
       SUBSTRING_INDEX(
          t.cvs
         , ','
       , number_generator.number
      )
      , ','
      , -1 
    ) , "'" , " AS Column", number_generator.number 
 ))
INTO @aggregateSQLPart                          
FROM (
   SELECT 
     @row := @row + 1 AS number
   FROM (
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
      ) row1
      CROSS JOIN (
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) row2
    CROSS JOIN (
      SELECT @row := 0 
    ) init_user_params 
  ) AS number_generator
 CROSS JOIN (
  SELECT 
     GROUP_CONCAT(t.col1 ORDER BY col1 ASC) AS cvs
   , COUNT(*) AS t_count
  FROM 
   t
 ) AS t

WHERE
 number BETWEEN 1 AND t_count;

SELECT @aggregateSQLPart;    


SET @SQL = CONCAT("
 SELECT 
 "
 , @aggregateSQLPart                                                 
);

SELECT @SQL;    

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Result

| Column1 | Column2 | Column3 | Column4 | Column5 |
| ------- | ------- | ------- | ------- | ------- |
| A       | B       | C       | D       | E       |

see demo

Note do not doubt about performance it runs in about a average off 5-10 ms in total on a "test" server.. Also note i select the user variables so you can see whats happing in between.

O...M...G +1. Actually, I prefer just `SELECT 'A' AS Column1, 'B' AS Column2 etc.` — Tim Biegeleisen, May 28 '19 at 16:16
me to @TimBiegeleisen but this was the only method i could thing of making it 100% dynamic. — Raymond Nijland, May 28 '19 at 16:22

Converting row to columns in mysql

2 Answers2