CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Somashekhar
Somashekhar

Reputation: 79

Split value into rows based on delimiter

Spliting the given input value from table into next row (need to split each exactly after 3rd )

Input

Temp (column/Table1)
TBL101 | PC | 1.00 | COMP101 | CS | 1.00 | TQR101 | CP | 5.00 | TXL101 | PC | 1.00 | SQL101 | PC | 1.00........etc

Output

 ID          Pack    qty (columns/Table2)

TBL101       PC      1.00
COMP101      CS      1.00
TQR101       CP      5.00
TXL101       PC      1.00
SQL101       PC      1.00

I'm using the below code to do this but only for first row it is working

DECLARE @Delimiter VARCHAR(40)
SET @Delimiter = '|'
;WITH CTE AS
(
    SELECT
        CAST('<M>' + REPLACE(temp, @Delimiter , '</M><M>') + '</M>' AS XML)
        AS [ColName XML]
    FROM Table1
)
--INSERT INTO Table2
  -- (ID,PACK,OrderQty)
SELECT
 [ColName XML].value('/M[1]', 'bigint') As [ID],
 [ColName XML].value('/M[2]', 'VARCHAR(40)') As [Pack},
 [ColName XML].value('/M[3]', 'decimal(18,2)') As [OrderQty]

FROM CTE
GO

Upvotes: 2

Views: 333

Answers (2)

gofr1
gofr1

Reputation: 15977

You can use this:

DECLARE @str nvarchar(max) = N'TBL101 | PC | 1.00 | COMP101 | CS | 1.00 | TQR101 | CP | 5.00 | TXL101 | PC | 1.00 | SQL101 | PC | 1.00',
        @x xml

SELECT @x = CAST('<a>'+REPLACE(@str,' | ', '</a><a>')+'</a>' as xml)

;WITH cte AS (
SELECT  t.c.value('.','nvarchar(100)') as [values],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as rn
FROM @x.nodes('/a') as t(c)
)

SELECT  [1] as ID1,
        [2] as Pack1,
        [0] as OrderQty1
FROM (
    SELECT  rn- CASE WHEN rn%3 = 0 THEN 3 ELSE rn%3 END as seq,
            rn%3 as s,
            [values]
    FROM cte
) as t
PIVOT (
    MAX([VALUES]) FOR s IN ([1],[2],[0])
) as pvt

Output:

ID1     Pack1   OrderQty1
TBL101  PC      1.00
COMP101 CS      1.00
TQR101  CP      5.00
TXL101  PC      1.00
SQL101  PC      1.00

At first convert into simple XML. Then add ROW_NUMBER() with SELECT NULL (read here about this trick) it will add some id to every row. Then we play with row numbers to get some sequence, so we can pivot that results.

EDIT

If you are using table, then make whole table an XML like:

DECLARE @temptable TABLE (
    Column1 nvarchar(max)
)

INSERT INTO @temptable VALUES
(N'TBL101 | PC | 1.00 | COMP101 | CS | 1.00 | TQR101 | CP | 5.00 | TXL101 | PC | 1.00 | SQL101 | PC | 1.00')

DECLARE @x xml

SELECT @x = (
    SELECT CAST('<a>'+REPLACE(Column1,' | ', '</a><a>')+'</a>' as xml)
    FROM @temptable
    FOR XML PATH('')
)

Then goes part with CTE as mentioned above.

Upvotes: 1

TT.
TT.

Reputation: 16137

Update: Version for dynamic number of fields in the string. No explanation here, if you want some ask in comments. Enjoy.

CREATE TABLE #dta(
    r NVARCHAR(4000) NOT NULL
);

INSERT INTO #dta(r)VALUES
    ('TBL101 | PC | 1.00 | COMP101 | CS | 1.00 | TQR101 | CP | 5.00 | TXL101 | PC | 1.00 | SQL101 | PC | 1.00'),
    ('TBL102 | PC | 4.00 | COMP102 | CS | 3.00 | TQR102 | CP | 6.00 | TXL102 | PC | 7.00 | SQL102 | PC | 9.00');

DECLARE @num_fields INT;

SELECT
    @num_fields=MAX(LEN(r) - LEN(REPLACE(r,'|',''))) + 1
FROM
    #dta;

DECLARE @fields_sel NVARCHAR(MAX);

SET @fields_sel=STUFF((
SELECT 
 ',[ColName XML].value(''/M['+CAST((N-1)*3+1 AS VARCHAR)+']'', ''NVARCHAR(40)'') As [ID'+CAST(N AS VARCHAR)+']'+
 ',[ColName XML].value(''/M['+CAST((N-1)*3+2 AS VARCHAR)+']'', ''NVARCHAR(40)'') As [Pack'+CAST(N AS VARCHAR)+']'+
 ',[ColName XML].value(''/M['+CAST((N-1)*3+3 AS VARCHAR)+']'', ''decimal(18,2)'') As [OrderQty'+CAST(N AS VARCHAR)+']'
FROM (
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
) AS tally(N)
WHERE N<=@num_fields/3
FOR XML PATH('')),1,1,'');

DECLARE @ca_sel NVARCHAR(MAX);

SET @ca_sel=STUFF((
SELECT
    ' UNION ALL SELECT RTRIM(LTRIM([ID'+CAST(N AS VARCHAR)+'])),RTRIM(LTRIM([Pack'+CAST(N AS VARCHAR)+'])),[OrderQty'+CAST(N AS VARCHAR)+']'
FROM (
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
) AS tally(N)
WHERE N<=@num_fields/3
FOR XML PATH('')),1,LEN(' UNION ALL'),'');

--SELECT @ca_sel;

--SELECT @fields_sel;

DECLARE @Delimiter NVARCHAR(40)
SET @Delimiter = N'|'

DECLARE @sql NVARCHAR(MAX);
SET @sql=N'
;WITH CTE([ColName XML]) AS
(
    SELECT
        CAST(''<M>'' + REPLACE(r, @Delimiter , ''</M><M>'') + ''</M>'' AS XML) AS [ColName XML]
    FROM 
        #dta
), sep_fields AS (
SELECT
    '+@fields_sel+N'
FROM CTE
)
SELECT
    up.*
FROM
    sep_fields
    CROSS APPLY (
        '+@ca_sel+N'
    ) AS up([ID],[Pack],[OrderQty])';

---SELECT @sql;

EXEC sp_executesql @sql, N'@Delimiter NVARCHAR(40)', @Delimiter;

DROP TABLE #dta;

Here's an answer for when you know beforehand how many fields will be present in the string field (eg in your question, 15):

CREATE TABLE #dta(
    r NVARCHAR(4000) NOT NULL
);

INSERT INTO #dta(r)VALUES
    ('TBL101 | PC | 1.00 | COMP101 | CS | 1.00 | TQR101 | CP | 5.00 | TXL101 | PC | 1.00 | SQL101 | PC | 1.00'),
    ('TBL102 | PC | 4.00 | COMP102 | CS | 3.00 | TQR102 | CP | 6.00 | TXL102 | PC | 7.00 | SQL102 | PC | 9.00');

DECLARE @Delimiter NVARCHAR(40)
SET @Delimiter = N'|'
;WITH CTE([ColName XML]) AS
(
    SELECT
        CAST('<M>' + REPLACE(r, @Delimiter , '</M><M>') + '</M>' AS XML) AS [ColName XML]
    FROM 
        #dta
), sep_fields AS (
SELECT
 [ColName XML].value('/M[1]', 'NVARCHAR(40)') As [ID1],
 [ColName XML].value('/M[2]', 'NVARCHAR(40)') As [Pack1],
 [ColName XML].value('/M[3]', 'decimal(18,2)') As [OrderQty1],
 [ColName XML].value('/M[4]', 'NVARCHAR(40)') As [ID2],
 [ColName XML].value('/M[5]', 'NVARCHAR(40)') As [Pack2],
 [ColName XML].value('/M[6]', 'decimal(18,2)') As [OrderQty2],
 [ColName XML].value('/M[7]', 'NVARCHAR(40)') As [ID3],
 [ColName XML].value('/M[8]', 'NVARCHAR(40)') As [Pack3],
 [ColName XML].value('/M[9]', 'decimal(18,2)') As [OrderQty3],
 [ColName XML].value('/M[10]', 'NVARCHAR(40)') As [ID4],
 [ColName XML].value('/M[11]', 'NVARCHAR(40)') As [Pack4],
 [ColName XML].value('/M[12]', 'decimal(18,2)') As [OrderQty4],
 [ColName XML].value('/M[13]', 'NVARCHAR(40)') As [ID5],
 [ColName XML].value('/M[14]', 'NVARCHAR(40)') As [Pack5],
 [ColName XML].value('/M[15]', 'decimal(18,2)') As [OrderQty5]
FROM CTE
)
SELECT
    up.*
FROM
    sep_fields
    CROSS APPLY (
        SELECT [ID1],[Pack1],[OrderQty1]
        UNION ALL
        SELECT [ID2],[Pack2],[OrderQty2]
        UNION ALL
        SELECT [ID3],[Pack3],[OrderQty3]
        UNION ALL
        SELECT [ID4],[Pack4],[OrderQty4]
        UNION ALL
        SELECT [ID5],[Pack5],[OrderQty5]
    ) AS up

DROP TABLE #dta;

Result is:

╔═══════════╦═══════╦═══════════╗
║    ID1    ║ Pack1 ║ OrderQty1 ║
╠═══════════╬═══════╬═══════════╣
║ TBL101    ║  PC   ║ 1.00      ║
║  COMP101  ║  CS   ║ 1.00      ║
║  TQR101   ║  CP   ║ 5.00      ║
║  TXL101   ║  PC   ║ 1.00      ║
║  SQL101   ║  PC   ║ 1.00      ║
║ TBL102    ║  PC   ║ 4.00      ║
║  COMP102  ║  CS   ║ 3.00      ║
║  TQR102   ║  CP   ║ 6.00      ║
║  TXL102   ║  PC   ║ 7.00      ║
║  SQL102   ║  PC   ║ 9.00      ║
╚═══════════╩═══════╩═══════════╝

Upvotes: 1

Related Questions