MSSQL converts a comma-delimited string into a column display

Table A
XM        XK
Zhang San      Chinese, history, music
John Doe      Sports, Chinese

Now I want to find out
XM        XK
Zhang San      language
Zhang San      history
Zhang San      music
John Doe      Sports
John Doe      language

1 Select2A.xm,xk=substring(A.xk,b. Number,charindex(',', A.XK+','B. Number)-B. Number) 3  from 4Table A AJoinMaster.. Spt_values b5      onB.type='P'  andB. Number between 1  and LEN(A.XK)6 where7      substring(','+A.xk,b. Number,1)=','

--1. Convert a string to a column displayif object_id('TB') is  not NULL Drop TableTBGo Create TableTb[numbering] varchar(3),[Product] varchar(2),[Quantity] int,[Unit Price] int,[Amount] int,[Serial Number] varchar(8)) Insert  intoTb[numbering],[Product],[Quantity],[Unit Price],[Amount],[Serial Number]) Select '001','AA',3,5, the,'12,13,14' Union  AllSelect '002','BB',8,9, -,'22,23,24'Go Select [numbering],[Product],[Quantity],[Unit Price],[Amount] ,substring([Serial Number]B. Number,charindex(',',[Serial Number]+','B. Number)-B. Number) as [Serial Number]  fromTB A with(NOLOCK), master. Spt_values b with(NOLOCK)whereB. Number>=1  andB. Number<=Len(A.[Serial Number]) andB.type='P' and substring(','+[Serial Number], Number,1)=','Go Drop TableTBGo /** Number Product quantity Unit price amount serial number-------------------------------------------------001 AA 3 5 001 AA 3 5 001 AA 3 5 1   5 002 BB 8 9 002 bb 8 9 002 bb 8 9*/ ---------- 

--7. Displaying strings as rows and columnsif object_id('TB') is  not NULL 
Drop TableTB
Create TableTb
(
Idint Identity(1,1),
Snvarchar( -)
) Insert  intoTB (s)Select 'Parking address 1, parking space 1| parking address 2, parking condition 2| parking address n, parking condition n';

 withCte as( Select substring(S, Number,charindex('|', S+'|', Number)- Number) asSS fromTb with(NOLOCK), master. Spt_values with(NOLOCK)whereType='P'  and  Number>=1  and  Number<=Len(s) and substring('|'+S Number,1)='|')
Select  Left(SS,charindex(',', SS)-1) asS1,substring(SS,charindex(',', SS)+1,Len(ss)) asS2 fromCte
Drop TableTB/** S1 s2-----------------------parking spaces 1 parking spaces 1 parking spaces 2 parking spaces 2 parking spaces n

MSSQL converts a comma-delimited string into a column display