dynamic transposition from rows to columns

Question

Need your help in query for the following scenario.

Table1

Name -ID1  - ID2 - ID3 - IDn  
A1  - NULL - 11 - 12  - nn  
A2  -  14  - NULL -11  -nn

The above table need to translated like below

Table2

Name - ID  
A1  - 11  
A1  - 12  
A2 - 14  
A2 - 11

The id values in table1 could be till ID50. The table2 would be the output based on table 1. All the null values would not be considered. What is the best way to do this. Any way to do this dynamically as the ID values would be atleast like ID1 - ID50

Can anyone help me with a query on how to do this. Thanks a lot

Is this a 'one-off' transformation? Table 2 is a much better design than Table 1 - and is row order in Table 2 important? — Strawberry, Jul 10 '18 at 15:28
Which do you care about more: less code, or faster execution? If the former, you'll have to write a loop to build dynamic sql and append the ID values into the query, then execute the query string. If the latter, you really are better off just sucking it up and hand-writing the SQL once for all 50 columns. — Joel Coehoorn, Jul 10 '18 at 15:28
check this link it has related points you may need https://stackoverflow.com/questions/20111418/sql-server-transpose-rows-to-columns — , Jul 10 '18 at 15:35
What's the table structure? Please post the `CREATE TABLE ...` statement. — The Impaler, Jul 10 '18 at 15:44
"you'll have to write a loop to build dynamic sql and append the ID values into the query, then execute the query string" Looping is not requirement in MySQL @JoelCoehoorn `GROUP_CONCAT` with `UNION ALL` as separator can generate the SQL you need check mine answer. — Raymond Nijland, Jul 10 '18 at 16:34

score 0 · Answer 1 · answered Jul 10 '18 at 16:29

It is possible with multiple SQL queries.

SET @SQL = NULL;

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT 
 GROUP_CONCAT(
   CONCAT(
     "SELECT "
     , "Name"
     , ", " , COLUMNS.COLUMN_NAME, " AS id"
     , " FROM "
     , COLUMNS.TABLE_NAME
     , " WHERE "
     , COLUMNS.COLUMN_NAME
     , " IS NOT NULL "
   )
   SEPARATOR ' UNION ALL '
 ) AS SQL_code
 INTO
   @SQL
FROM 
 information_schema.COLUMNS
WHERE
   COLUMNS.TABLE_NAME = 'Table1'
 AND
   COLUMNS.COLUMN_NAME <> 'Name';

SET @SQL := CONCAT(@SQL, " ORDER BY Name ASC"); 

PREPARE s FROM @SQL;
EXECUTE s;

see demo https://www.db-fiddle.com/f/jdcmYDxDLnEgBQ6YGJP1PH/1

dynamic transposition from rows to columns

1 Answers1