StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Create the new table with the following statements:</p> <pre><code>select @s:=concat('create table students_col (',group_concat(jntunno,' CHAR(10)' order by slno),')') from students; prepare stmt from @s; execute stmt; deallocate prepare stmt; </code></pre> <p>observe how the <code>CREATE TABLE</code> is constructed using the <code>group_concat</code> </p> <p>Demo: <a href="http://sqlfiddle.com/#!2/872b1/1" rel="nofollow">SQL Fiddle</a></p> <p>In case you also want to insert the names, this is the statement to it:</p> <pre><code>select @s:=concat('insert into students_col values (',group_concat(concat('"',name,'"') order by slno),')') from students; prepare stmt from @s; execute stmt; deallocate prepare stmt; select * from students_col; </code></pre> <p>Here is my whole trail:</p> <pre><code>mysql> drop table if exists students; Query OK, 0 rows affected (0.00 sec) mysql> create table students (slno integer, jntunno char(10), name varchar(50)); Query OK, 0 rows affected (0.07 sec) mysql> insert into students values (1,'1134A0501','ADARI GOPI'); Query OK, 1 row affected (0.00 sec) mysql> insert into students values (2,'1134A0502','BALU'); Query OK, 1 row affected (0.00 sec) mysql> insert into students values (3,'1134A0503','GEETHA'); Query OK, 1 row affected (0.00 sec) mysql> drop table if exists students_col; Query OK, 0 rows affected (0.00 sec) mysql> select @s:=concat('create table students_col (',group_concat(jntunno,' CHAR(10)' order by slno),')') from students; +-----------------------------------------------------------------------------------------------+ | @s:=concat('create table students_col (',group_concat(jntunno,' CHAR(10)' order by slno),')') | +-----------------------------------------------------------------------------------------------+ | create table students_col (1134A0501 CHAR(10),1134A0502 CHAR(10),1134A0503 CHAR(10)) | +-----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> prepare stmt from @s; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> execute stmt; Query OK, 0 rows affected (0.21 sec) mysql> deallocate prepare stmt; Query OK, 0 rows affected (0.01 sec) mysql> mysql> select @s:=concat('insert into students_col values (',group_concat(concat('"',name,'"') order by slno),')') from students; +------------------------------------------------------------------------------------------------------+ | @s:=concat('insert into students_col values (',group_concat(concat('"',name,'"') order by slno),')') | +------------------------------------------------------------------------------------------------------+ | insert into students_col values ("ADARI GOPI","BALU","GEETHA") | +------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> prepare stmt from @s; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> execute stmt; Query OK, 1 row affected (0.00 sec) mysql> deallocate prepare stmt; Query OK, 0 rows affected (0.00 sec) mysql> mysql> select * from students_col; +------------+-----------+-----------+ | 1134A0501 | 1134A0502 | 1134A0503 | +------------+-----------+-----------+ | ADARI GOPI | BALU | GEETHA | +------------+-----------+-----------+ 1 row in set (0.00 sec) mysql> </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload