· 8 years ago · Jul 30, 2018, 03:38 PM
1MySQL - turn one long table column into table with multiple columns
2COL A
3-----
4A
5B
6C
7D
8E
9F
10G
11
12COL A COL B COL C COL D
13----- ----- ------ ------
14A B C D
15E F G H
16I J K L
17
18CREATE TABLE mytable
19(
20 X VARCHAR(10)
21);
22
23mysql> drop database if exists user1267617;
24Query OK, 3 rows affected (0.10 sec)
25
26mysql> create database user1267617;
27Query OK, 1 row affected (0.01 sec)
28
29mysql> use user1267617
30Database changed
31mysql> CREATE TABLE mytable (X VARCHAR(10)
32 -> );
33Query OK, 0 rows affected (0.11 sec)
34
35mysql>
36mysql> insert into mytable values
37 -> ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),
38 -> ('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),
39 -> ('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z');
40Query OK, 26 rows affected (0.06 sec)
41Records: 26 Duplicates: 0 Warnings: 0
42
43mysql> select * from mytable;
44+------+
45| X |
46+------+
47| A |
48| B |
49| C |
50| D |
51| E |
52| F |
53| G |
54| H |
55| I |
56| J |
57| K |
58| L |
59| M |
60| N |
61| O |
62| P |
63| Q |
64| R |
65| S |
66| T |
67| U |
68| V |
69| W |
70| X |
71| Y |
72| Z |
73+------+
7426 rows in set (0.00 sec)
75
76mysql>
77
78CREATE TABLE mytmp
79(
80 id int not null auto_increment,
81 X varchar(10),
82 groupnum int,
83 groupndx int,
84 primary key (id)
85);
86INSERT INTO mytmp (X) select X from mytable;
87UPDATE mytmp SET groupndx = MOD(id - 1,4),groupnum = FLOOR((id - 1)/4);
88alter table mytmp add index (groupnum);
89select * from mytmp;
90CREATE TABLE mynewtable
91(
92 id int not null auto_increment,
93 groupnum int,
94 colA varchar(10) default '',
95 colB varchar(10) default '',
96 colC varchar(10) default '',
97 colD varchar(10) default '',
98 key (groupnum),
99 primary key (id)
100);
101insert into mynewtable (colA,groupnum)
102select X,groupnum from mytmp where groupndx = 0;
103update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
104AND B.groupndx=1 set A.colB = B.x;
105update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
106AND B.groupndx=2 set A.colC = B.x;
107update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
108AND B.groupndx=3 set A.colD = B.x;
109alter table mynewtable drop column groupnum;
110select * from mynewtable;
111
112mysql> CREATE TABLE mytmp
113 -> (
114 -> id int not null auto_increment,
115 -> X varchar(10),
116 -> groupnum int,
117 -> groupndx int,
118 -> primary key (id)
119 -> );
120Query OK, 0 rows affected (0.14 sec)
121
122mysql> INSERT INTO mytmp (X) select X from mytable;
123Query OK, 26 rows affected (0.07 sec)
124Records: 26 Duplicates: 0 Warnings: 0
125
126mysql> UPDATE mytmp SET groupndx = MOD(id - 1,4),groupnum = FLOOR((id - 1)/4);
127Query OK, 26 rows affected (0.06 sec)
128Rows matched: 26 Changed: 26 Warnings: 0
129
130mysql> alter table mytmp add index (groupnum);
131Query OK, 0 rows affected (0.23 sec)
132Records: 0 Duplicates: 0 Warnings: 0
133
134mysql> select * from mytmp;
135+----+------+----------+----------+
136| id | X | groupnum | groupndx |
137+----+------+----------+----------+
138| 1 | A | 0 | 0 |
139| 2 | B | 0 | 1 |
140| 3 | C | 0 | 2 |
141| 4 | D | 0 | 3 |
142| 5 | E | 1 | 0 |
143| 6 | F | 1 | 1 |
144| 7 | G | 1 | 2 |
145| 8 | H | 1 | 3 |
146| 9 | I | 2 | 0 |
147| 10 | J | 2 | 1 |
148| 11 | K | 2 | 2 |
149| 12 | L | 2 | 3 |
150| 13 | M | 3 | 0 |
151| 14 | N | 3 | 1 |
152| 15 | O | 3 | 2 |
153| 16 | P | 3 | 3 |
154| 17 | Q | 4 | 0 |
155| 18 | R | 4 | 1 |
156| 19 | S | 4 | 2 |
157| 20 | T | 4 | 3 |
158| 21 | U | 5 | 0 |
159| 22 | V | 5 | 1 |
160| 23 | W | 5 | 2 |
161| 24 | X | 5 | 3 |
162| 25 | Y | 6 | 0 |
163| 26 | Z | 6 | 1 |
164+----+------+----------+----------+
16526 rows in set (0.00 sec)
166
167mysql> CREATE TABLE mynewtable
168 -> (
169 -> id int not null auto_increment,
170 -> groupnum int,
171 -> colA varchar(10) default '',
172 -> colB varchar(10) default '',
173 -> colC varchar(10) default '',
174 -> colD varchar(10) default '',
175 -> key (groupnum),
176 -> primary key (id)
177 -> );
178Query OK, 0 rows affected (0.11 sec)
179
180mysql> insert into mynewtable (colA,groupnum)
181 -> select X,groupnum from mytmp where groupndx = 0;
182Query OK, 7 rows affected (0.07 sec)
183Records: 7 Duplicates: 0 Warnings: 0
184
185mysql> update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
186 -> AND B.groupndx=1 set A.colB = B.x;
187AND B.groupndx=3 set A.colD = B.x;
188Query OK, 7 rows affected (0.07 sec)
189Rows matched: 7 Changed: 7 Warnings: 0
190
191mysql> update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
192 -> AND B.groupndx=2 set A.colC = B.x;
193Query OK, 6 rows affected (0.06 sec)
194Rows matched: 6 Changed: 6 Warnings: 0
195
196mysql> update mynewtable A INNER JOIN mytmp B ON A.groupnum=B.groupnum
197 -> AND B.groupndx=3 set A.colD = B.x;
198Query OK, 6 rows affected (0.06 sec)
199Rows matched: 6 Changed: 6 Warnings: 0
200
201mysql> alter table mynewtable drop column groupnum;
202Query OK, 7 rows affected (0.26 sec)
203Records: 7 Duplicates: 0 Warnings: 0
204
205mysql> select * from mynewtable;
206+----+------+------+------+------+
207| id | colA | colB | colC | colD |
208+----+------+------+------+------+
209| 1 | A | B | C | D |
210| 2 | E | F | G | H |
211| 3 | I | J | K | L |
212| 4 | M | N | O | P |
213| 5 | Q | R | S | T |
214| 6 | U | V | W | X |
215| 7 | Y | Z | | |
216+----+------+------+------+------+
2177 rows in set (0.00 sec)
218
219mysql>