SQL separate for stored procedure

About SQL Server Management Studio stored procedure.

The following variables ‘|’ I want to separate it from. How can I do it?

'628391|28100|8304|3|1201129|12|Kg|M01|SERIOUSLY CHUNKY WOOL' 
'627452|70462|618|60|100059|72|Ad|M01|THICK & QUICK STRIPES'
'617213|99233|89|10|18754|12|Kg|M01|FASHION KC ARAN 400'

Answer

You can use the following query

 DECLARE @PL AS VARCHAR(MAX)='628391|28100|8304|3|1201129|12|Kg|M01|SERIOUSLY CHUNKY WOOL' 


  SELECT value FROM string_split(@PL,'|')


+-----------------------+
|         value         |
+-----------------------+
| 628391                |
| 28100                 |
| 8304                  |
| 3                     |
| 1201129               |
| 12                    |
| Kg                    |
| M01                   |
| SERIOUSLY CHUNKY WOOL |
+-----------------------+

EDIT

DROP TABLE IF EXISTS TestStrList
DECLARE  @query  AS NVARCHAR(MAX) 
DECLARE @PL AS VARCHAR(MAX)='628391|28100|8304|3|1201129|12|Kg|M01|SERIOUSLY CHUNKY WOOL' 
DECLARE @cOL AS VARCHAR(MAX)

SELECT value INTO 
TestStrList FROM string_split(@PL,'|')



select @cOL = STUFF((SELECT ',' + QUOTENAME(value) 
                    FROM TestStrList
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cOL + N' from 
             (
                select  value
                from TestStrList
            ) x
            pivot 
            (
                max(value)
                for value in (' + @cOL + N')
            ) p '



exec sp_executesql @query;



+--------+-------+------+---+---------+----+----+-----+-----------------------+
| 628391 | 28100 | 8304 | 3 | 1201129 | 12 | Kg | M01 | SERIOUSLY CHUNKY WOOL |
+--------+-------+------+---+---------+----+----+-----+-----------------------+
| 628391 | 28100 | 8304 | 3 | 1201129 | 12 | Kg | M01 | SERIOUSLY CHUNKY WOOL |
+--------+-------+------+---+---------+----+----+-----+-----------------------+