Search code examples
mysqlsqlsql-query-store

Sql query to merge all columns into a single column

  id college1  college2  college3
   1 abc       xyz        rst

Above is the input-

Looking for output :-

id college1  college2  college3
 1  abc 
 1  xyz
 1  rst
Solution

  • One method is simply union all:

    select id, college1 as college from t
union all
select id, college2 as college from t
union all
select id, college3 as college from t;

    This requires scanning the table three times, which is usually fine. But if "t" is really a complicated query or really big table, there are more efficient approaches.