Table data below
ID TYPE T1 T2, T3, T4
1 in Shanghai, Beijing, China S S B B

want to inquiry into the following
ID TYPE T1 T2, T3, T4
1 Shanghai S NULL NULL S
1 Beijing NULL NULL B B

CodePudding user response:

Create table test (id int, ttype varchar (50), t1 varchar (10), t2 varchar (10), t3 varchar (10), t4 varchar (10))
Insert into test
Values (1, 'Shanghai, Beijing,' S ', 'B', 'S', 'B')


SELECT id,
SUBSTRING_INDEX (SUBSTRING_INDEX (ttype, 'and' help_topic_id + 1), ', ', 1) AS num
The case when help_topic_id + 1=1 then t1 else null end as t1
That case when help_topic_id + 1=2 then t2 else null end as t2
That case when help_topic_id + 1=1 then t3 else null end as t3
The case when help_topic_id + 1=2 then t4 else null end as t4
The FROM
Mysql help_topic, test
WHERE
Help_topic_id & lt; 2

CodePudding user response:

1 Shanghai S null null S
1 Beijing null null B B