Transpose columns into rows in mysql

Question

need help configuring the query below:

Query1:

SELECT @s:= @s + 1 serial_number,`COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS`, (SELECT @s:= 0) AS s WHERE `TABLE_SCHEMA`='2014rawfiles' AND `TABLE_NAME`='012014actives';

Query1 output:

| serial_number | COLUMN_NAME | | 1 | ID | | 2 | FirstName | | 3 | LastName | ... up to 89 rows ...

I am trying to transpose the output of this query to look like this:

| 1 | 2 | 3 | | ID | FirstName | LastName |

with the serial_number as header and the COLUMN_NAME as a single row. Any ideas?

Thanks in advance!

peterm · Accepted Answer · 2014-11-06 05:46:44Z

If you really need this you can do it with dynamic SQL

SET @sql = NULL; SELECT GROUP_CONCAT(CONCAT('''',column_name, ''' `', @n := @n + 1, '`')) INTO @sql FROM information_schema.columns CROSS JOIN (SELECT @n := 0) i WHERE table_schema = SCHEMA() AND table_name = '012014actives' ORDER BY ordinal_position; SET @sql = CONCAT('SELECT ', @sql); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

Output:

 | 1 | 2 | 3 | |----|-----------|----------| | Id | FirstName | LastName |

Here is a SQLFiddle demo

Gaurav Jain · Accepted Answer · 2014-11-03 05:34:03Z

There is no direct way to do this.But you can choose an alternate for this which I am going to tell
you.

you can not split values to column but can merge them in a single column and can further split them into your front end.

SELECT group_concat(serial_number) from pivot union all SELECT group_concat(COLUMN_NAME) from
pivot

by this you will get result like this

1,2,3

ID, FirstName, LastName

Collectives™ on Stack Overflow

Transpose columns into rows in mysql

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related