What are the SQL skills of row-column transformation in MySQL

This article is about SQL techniques for column conversion in MySQL. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

Common scenes of row and column transition

Because many business tables use design patterns that violate the first normal form for historical or performance reasons. That is, multiple attribute values are stored in the same column (see the following table for specific structure). In this pattern, applications often need to divide the column by separators and get the result of column rotation.

Table data:

ID

Value

1

tiny,small,big

2

small,medium

3

tiny,big

Expect results:

ID

Value

1

tiny

1

small

1

big

2

small

2

medium

3

tiny

3

big

specific methods

Let's start with a concrete example:

#Prepare sample data

create table tbl_name (ID int ,mSize varchar(100));

insert into tbl_name values (1,'tiny,small,big');

insert into tbl_name values (2,'small,medium');

insert into tbl_name values (3,'tiny,big');

#Self-increasing table for column conversion cycle

create table incre_table (AutoIncreID int);

insert into incre_table values (1);

insert into incre_table values (2);

insert into incre_table values (3);

#SQL for row and column conversion

select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1)

from

tbl_name a

join

incre_table b

on b.AutoIncreID