Converts a row with commas stored in a column to multiple lines without commas

The table and the data are as follows:

DECLARE @T TABLE (id int,col1 VARCHAR (), Col2 VARCHAR (50))
INSERT into @T
SELECT 1, ' unloading charge ', ' transportation fleet business, customer service receipt business '
UNION All
SELECT 2, ' Declaration fee ', ' Customs Service, customer service receipt business '

--SQL
Select Num=row_number () over (order by GETDATE ()), t.*,newcol2=substring (col2+ ', ', Number,charindex (', ', ', col2+ ', ', '), Number)-number)
From @T T,master. Spt_values
Where type= ' P ' and number>0 and SUBSTRING (', ' +col2,number,1) = ', '

About Master.. Spt_values's role is seen in the full excerpt of the written comparison as follows (excerpt from http://www.cnblogs.com/kingboy2008/p/3978007.html):

Select number from Master. Spt_values with (nolock) where type= ' P '
/** explained: Master. The corresponding Number field value for the Spt_values table's field value is p from the 0-2047*/


--1. Convert a string to a column display

If object_id (' TB ') is not null drop table TB
Go
CREATE table TB ([numbered] varchar (3), [Products] varchar (2), [quantity] int,[unit Price] int,[amount] int,[serial number] varchar (8))
INSERT into TB ([number],[product],[quantity],[price],[amount],[serial number])
Select ' 001 ', ' AA ', 3,5,15, ' 12,13,14 ' UNION ALL
Select ' 002 ', ' BB ', 8,9,13, ' 22,23,24 '
Go
Select [Number],[product],[quantity],[price],[amount]
, substring ([serial number],b.number,charindex (', ', [serial number]+ ', ', B.number)-b.number) as [serial number]
From TB A with (NOLOCK), master. Spt_values B with (NOLOCK)
where B.number>=1 and B.number<len (a.[serial number]) and b.type= ' P '
and substring (', ' +[serial number],number,1) = ', '
Go
DROP table TB
Go
/**
Number Product quantity Unit price amount serial number
---- ---- ----------- ----------- ----------- --------
AA 3 5 15 12
AA 3 5 15 13
AA 3 5 15 14
BB 8 9 13 22
BB 8 9 13 23
BB 8 9 13 24
*/
----------

--2. A string before the fourth comma
DECLARE @str varchar (100)
Set @str = ' 10,102,10254,103265,541,2154,41,156 '
; with CTE as (
Select Left (@str, number-1) as Ss,row_number () over (order by GETDATE ()) as XH
From Master.. Spt_values with (NOLOCK)
where Number>=1 and Number<=len (@str + ', ') and type= ' P '
and substring (@str + ', ', number,1) = ', '
) Select SS from CTE where xh=4
/**
Ss
-------------------
10,102,10254,103265
*/
----------


--3. Find the same characters in two sentences
DECLARE @Lctext1 varchar (100)
DECLARE @Lctext2 varchar (100)
Set @Lctext1 = ' We are all friends from corners '
Set @Lctext2 = ' Is it really good to go with friends? '
Select substring (@Lctext2, number,1) as value
From Master.. Spt_values with (NOLOCK)
where type= ' P ' and Number>=1 and Number<=len (@Lctext2)
and CHARINDEX (substring (@Lctext2, number,1), @Lctext1, number) >1
/**
Value
-----
Friends
Friends
Of
*/
---------


--4. Extract all months between two dates
If object_id (' TB ') is not null drop table TB
Go
CREATE table TB (ID int identity (1,1), StartDate varchar (a), EndDate varchar (10))
INSERT into TB (startdate,enddate) Select ' 2013-01-01 ', ' 2013-09-25 '
Go
DECLARE @startDate varchar (10)
DECLARE @endDate varchar (10)
Select @startDate =startdate, @endDate =enddate from TB with (NOLOCK)
Select CONVERT (varchar (7), DateAdd (Mm,number, @startDate), as [month]
From Master.. Spt_values with (NOLOCK)
where type= ' P ' and number>=0
and DateAdd (Mm,number, @startDate) <= @endDate
Go
DROP table TB
Go
/**
Month
-------
2013-01
2013-02
2013-03
2013-04
2013-05
2013-06
2013-07
2013-08
2013-09
*/
---------


--5. Find all dates for the month of a date
DECLARE @date datetime
Set @date = ' 2013-08-31 '
Select CONVERT (char (7), @date,) + '-' +right (' 0 ' +convert (varchar (2), number), 2) as [date format 1]
, LTrim (@date)) +right (100+month (@date), 2) +right (' 0 ' +ltrim (number), 2) as [date format 2]
From Master.. Spt_values with (NOLOCK)
where type= ' P ' and number>=1
--and Number<=datediff (DD, @date, DATEADD (mm,1, @date))--for MSSQL, the statement is not tested in 2013-08-31, when there was no number 31st in September, solid calculated the number of days is 30 days
and Number<=datediff (Dd,convert (char (7), @date,) + ' -01 ', convert (char (7), DateAdd (mm,1, @date), 120) + '-01 ')-- Convert to number 1th to calculate the number of days
/**
Date format 1st period format 2
----------- --------------------
2013-08-01 20130801
2013-08-02 20130802
2013-08-03 20130803
2013-08-04 20130804
2013-08-05 20130805
2013-08-06 20130806
2013-08-07 20130807
2013-08-08 20130808
2013-08-09 20130809
2013-08-10 20130810
2013-08-11 20130811
2013-08-12 20130812
2013-08-13 20130813
2013-08-14 20130814
2013-08-15 20130815
2013-08-16 20130816
2013-08-17 20130817
2013-08-18 20130818
2013-08-19 20130819
2013-08-20 20130820
2013-08-21 20130821
2013-08-22 20130822
2013-08-23 20130823
2013-08-24 20130824
2013-08-25 20130825
2013-08-26 20130826
2013-08-27 20130827
2013-08-28 20130828
2013-08-29 20130829
2013-08-30 20130830
2013-08-31 20130831
*/
---------


--6. According to a given time as the basis of 2-hour division, to draw a day divided by the time period
DECLARE @time varchar (5)
Set @time = ' 11:13 '
Select LTrim (A.number) +right (@time, 3) + '-' +ltrim (b.number) +right (@time, 3) as [partition result]
From Master.. Spt_values A With (NOLOCK), master. Spt_values B with (NOLOCK)
Where a.type= ' P ' and b.type= ' P '
and A.number>=left (@time, 2) and b.number<=24
and A.number+2=b.number
/**
Dividing the results
-----------------------------------
11:13-13:13
12:13-14:13
13:13-15:13
14:13-16:13
15:13-17:13
16:13-18:13
17:13-19:13
18:13-20:13
19:13-21:13
20:13-22:13
21:13-23:13
22:13-24:13
*/
---------


--7. Display a string as a row
If object_id (' TB ') is not null drop table TB
CREATE table TB (ID int identity (1,1), S nvarchar (100))
INSERT into TB (s) SELECT ' parking address 1, parking condition 1| parking address 2, parking condition 2| parking address n, parking condition n '
; with CTE as (
Select substring (s,number,charindex (' | ', s+ ' | ', number)-number) as SS
From TB with (NOLOCK), master. Spt_values with (NOLOCK)
where type= ' P ' and Number>=1 and Number<=len (s)
and substring (' | ') +s,number,1) = ' | '
Select Left (Ss,charindex (', ', ss)-1) as s1,substring (Ss,charindex (', ', ss) +1,len (ss)) as S2 from CTE
DROP table TB
/**
S1 s2
----------- ------------
Parking Address 1 Parking space 1
Parking Address 2 parking space 2
Parking address n parking situation n
* *