SQL server how to change pipe separated column string to rows

Question

I have 3 columns in a table, in which 2 columns have string separated by a '|' pipe. Both these columns values depend on each other.

For an example: I have data in the table like this :

ID  product quantity
1   A|B|C   1|2|3
2   X|Y|Z   7|8|9

I would like to change it to something like this :

ID  product quantity
1   A   1
1   B   2
1   C   3
2   X   7
2   Y   8
2   Z   9

As i am working with SSMS, i don't have any other choice except SQL. I try to use cross apply but i am not getting right result. For 1 row i receive 9 rows instead of getting 3. could anyone suggest me which method should i use?

Thank you in advance!! JACK

Answer 1

splitting strings is easy, there are tons of examples. The tricky part here is to connect the fragments via their position. My suggestion uses XMLs abilities to target an element by its position:

DECLARE @tbl TABLE(ID INT,  product VARCHAR(100) , quantity VARCHAR(100) )
INSERT INTO @tbl VALUES 
 (1   ,'A|B|C' ,  '1|2|3')
,(2   ,'X|Y|Z' ,  '7|8|9');

--This is the query

WITH CastedToXML AS
(
    SELECT *
         ,CAST('<x>' + REPLACE(product,'|','</x><x>') + '</x>' AS XML) AS ProductXml
         ,CAST('<x>' + REPLACE(quantity,'|','</x><x>') + '</x>' AS XML) AS QuantityXml
    FROM @tbl
)
SELECT *
      ,ProductXml.value('/x[sql:column("Nmbr")][1]','nvarchar(10)') AS ProductAtPosition
      ,QuantityXml.value('/x[sql:column("Nmbr")][1]','int') AS QuantityAtPosition
FROM CastedToXML
--Create a set of running numbers (spt_values is just a pre-filled table with many rows)
CROSS APPLY (SELECT TOP(CastedToXML.ProductXml.value('count(/x)','int')) 
             ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
             FROM master..spt_values) AS Tally(Nmbr);

the result

+----+------+-------------------+--------------------+
| ID | Nmbr | ProductAtPosition | QuantityAtPosition |
+----+------+-------------------+--------------------+
| 1  | 1    | A                 | 1                  |
+----+------+-------------------+--------------------+
| 1  | 2    | B                 | 2                  |
+----+------+-------------------+--------------------+
| 1  | 3    | C                 | 3                  |
+----+------+-------------------+--------------------+
| 2  | 1    | X                 | 7                  |
+----+------+-------------------+--------------------+
| 2  | 2    | Y                 | 8                  |
+----+------+-------------------+--------------------+
| 2  | 3    | Z                 | 9                  |
+----+------+-------------------+--------------------+

Some explanation:

the cast to xml transfers your A|B|C to

<x>A</x>
<x>B</x>
<x>C</x>

This list is joined with a number set created on the fly using the count of <x> as the TOP limit.

Now it is easy to pick the <x> out of your XML by the position.

Try it out!

UPDATE: Non-unique IDs

DECLARE @tbl TABLE(ID INT,  product VARCHAR(100) , quantity VARCHAR(100) )
INSERT INTO @tbl VALUES 
 (1   ,'A|B|C' ,  '1|2|3')
,(2   ,'X|Y|Z' ,  '7|8|9')
,(3   ,'a|b|c' ,  '7|8|9')
,(2   ,'D|e|f' ,  '7|8|9')
;
--This is the query

WITH CastedToXML AS
(
    SELECT *
         ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RowIndex
         ,CAST('<x>' + REPLACE(product,'|','</x><x>') + '</x>' AS XML) AS ProductXml
         ,CAST('<x>' + REPLACE(quantity,'|','</x><x>') + '</x>' AS XML) AS QuantityXml
    FROM @tbl
)
SELECT *
      ,ProductXml.value('/x[sql:column("Nmbr")][1]','nvarchar(10)') AS ProductAtPosition
      ,QuantityXml.value('/x[sql:column("Nmbr")][1]','int') AS QuantityAtPosition
FROM CastedToXML
--Create a set of running numbers (spt_values is just a pre-filled table with many rows)
CROSS APPLY (SELECT TOP(CastedToXML.ProductXml.value('count(/x)','int')) 
             ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
             FROM master..spt_values) AS Tally(Nmbr);

Answer 2

Test Data

CREATE TABLE #t (ID INT,  product VARCHAR(100) , quantity VARCHAR(100) )
INSERT INTO #t VALUES 
(1   ,'A|B|C' ,  '1|2|3'),
(2   ,'X|Y|Z' ,  '7|8|9');

Query

WITH Products AS (
        SELECT    ID
                , Product_Split.a.value('.', 'VARCHAR(100)') Products
                , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) rn
        FROM (
              SELECT  ID
                     ,Cast ('<X>' 
                           + Replace(product, '|', '</X><X>') 
                           + '</X>' AS XML) AS Product_Data
                FROM #t
            ) AS t 
        CROSS APPLY Product_Data.nodes ('/X') AS Product_Split(a) 
),
 Quantities AS (
        SELECT    ID
                , Quantity_Split.a.value('.', 'VARCHAR(100)') Quantity
                , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) rn
        FROM (
                SELECT  ID
                        ,Cast ('<X>' 
                            + Replace(quantity, '|', '</X><X>') 
                            + '</X>' AS XML) AS Quantity_Data
                FROM #t
            ) AS t 
        CROSS APPLY Quantity_Data.nodes ('/X') AS Quantity_Split(a)
 )
 SELECT   t.ID
        , P.Products
        , Q.Quantity

 FROM #t t
 LEFT JOIN Products     P   ON t.ID = p.ID
 LEFT JOIN Quantities   Q   ON Q.ID = t.ID 
                            AND Q.rn = p.rn

Result Set

╔════╦══════════╦══════════╗
║ ID ║ Products ║ Quantity ║
╠════╬══════════╬══════════╣
║  1 ║ A        ║        1 ║
║  1 ║ B        ║        2 ║
║  1 ║ C        ║        3 ║
║  2 ║ X        ║        7 ║
║  2 ║ Y        ║        8 ║
║  2 ║ Z        ║        9 ║
╚════╩══════════╩══════════╝

Answer 3

This is rather tricky, because you need for the values to match up. The following takes a recursive CTE approach:

with cte as (
      select id,
             left(product, charindex('|', product + '|') - 1) as product,
             left(quantity, charindex('|', quantity + '|') - 1) as quantity,
             substring(product, charindex('|', product + '|') + 1, 1000) as products,
             substring(quantity, charindex('|', quantity + '|') + 1, 1000) as quantities
     from t
     union all
      select id,
             left(products, charindex('|', products + '|') - 1) as product,
             left(quantities, charindex('|', quantities + '|') - 1) as quantity,
             substring(products, charindex('|', products + '|') + 1, 1000) as products,
             substring(quantities, charindex('|', quantities + '|') + 1, 1000) as quantities
     from cte
     where products <> '' and quantities <> ''
    )
select id, product, quantity
from cte;

Here is a little Rextester.