SQL Server CROSS APPLY and OUTER APPLY

http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

T SQL Script - Splitting a delimited column value to multiple columns

Create Table Product(Col1 varchar(10),Col2 Varchar(1000))

iNSERT INTO pRODUCT(Col1,Col2) sELECT 1,'AR-5381 BA-8327 BB-7421 BB-8107 BB-9108'
iNSERT INTO pRODUCT(Col1,Col2) sELECT 2,'BC-M005 BC-R205 BE-2349 BE-2908 BK-M18B-40'

   
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT Col1, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5
FROM
(SELECT Col1,
        ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY nums.n) AS PositionInList,
        LTRIM(RTRIM(SUBSTRING(valueTable.Col2, nums.n, charindex(N' ', valueTable.Col2 + N' ', nums.n) - nums.n))) AS [Value]
 FROM   Numbers AS nums INNER JOIN Product AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Col2)) AND SUBSTRING(N' ' + valueTable.Col2, n, 1) = N' ') AS SourceTable
PIVOT
(
MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5])
) AS Table2

Drop table Product

Split one long comma string into multi column new table

CREATE TABLE Table1
([Field1] varchar(38))
;

INSERT INTO Table1
([Field1])
VALUES
('Apples,oranges, pears, berries, melons')
;

WITH Split_Fields (Field, xmlfields)
AS
(
    SELECT Field1 AS Field,
    CONVERT(XML,'<Fields><field>'
    + REPLACE(Field1,',', '</field><field>') + '</field></Fields>') AS xmlfields
      FROM Table1
)

 SELECT Field,    
 xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1,  
 xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2,
 xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3,  
 xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4,
 xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5
 FROM Split_Fields

SQL How to Split One Column WIth 2 Different Delimiters into Multiple Variable Columns

create table #MessyDelim
(DelimList varchar(255));

insert into #MessyDelim
Values ('30;120;100')
, ('50;60')
, ('75/10')
, ('115/50/20/10/5')
, ('80;65;40;23;12;10')
, ('100')
;WITH
MessyDelim AS --As Nenad Zivkovic suggested, standardize on one delimiter for simplicity
(   SELECT DelimList,REPLACE(DelimList,'/',';') AS String
    FROM #MessyDelim),
Split AS --Recursive CTE to produce strings each with one less delimited value attached
(   SELECT DelimList, 1 AS Sort, String + ';' AS String
    FROM MessyDelim
    UNION ALL
    SELECT DelimList, Sort+1, RIGHT(String,LEN(String)-CHARINDEX(';',String))
    FROM Split
    WHERE CHARINDEX(';',String) > 0 ),
Cleanup AS --Reduce strings to single delimited value each
(   SELECT DelimList,LEFT(String,CHARINDEX(';',String)-1) AS String, Sort
    FROM Split
    WHERE String <> '' )  
SELECT DelimList, --Pivot out into columns
    [1] AS DL1,
    [2] AS DL2,
    [3] AS DL3,
    [4] AS DL4,
    [5] AS DL5,
    [6] AS DL6
FROM Cleanup
PIVOT(MAX(String) FOR Sort IN ([1],[2],[3],[4],[5],[6])) pvt
ORDER BY DelimList
OPTION (MAXRECURSION 10) --Just for safety sake