0

Table name: D_order

OrderID    OrderName    
11,12        A:B
13,14        C:D

Output required

OrderID      OrderName    
11               A
12               B
13               C
14               D
Justin
  • 9,634
  • 6
  • 35
  • 47
Dhruv Red
  • 3
  • 1
  • 1
    Don't store data this way to begin with. That breaks the most basic design principle the 1st Normal Form. Even when you split those values, the server will have to scan the entire table because no index will contain the actual ID values, only the full strings. Forget about unique checks too. That's not a relational model disadvantage, that's just a design bug. Just *don't* do it. – Panagiotis Kanavos Jan 09 '20 at 07:38
  • Hi @DhruvRed, can you show us the code you have tried so far ? Cheers! – VBoka Jan 09 '20 at 07:40
  • BTW the "expected" results are arbitrary - where's `11 - B` ? By what logic was that pair eliminated? Even if you used a database with array support, there's no order relation between the values of different arrays. Apart from that, there are a *lot* of essentially duplicate questions that show how to split strings. Supported SQL Server versions (even 2014 has gone out of mainstream support) provide `STRING_SPLIT`. Restricting values based on element order though is going to be a real pain, both in code and performance. – Panagiotis Kanavos Jan 09 '20 at 07:44

3 Answers3

0

Recursive function might help
check this Answer https://stackoverflow.com/a/5493616/6631280

or Check this fiddle http://sqlfiddle.com/#!18/f54fd/10

;WITH tmp(OrderID, OrderName, String) AS
    (
        SELECT
            OrderID,
            OrderName,
            LEFT(String, CHARINDEX(',', String + ',') - 1),
            STUFF(String, 1, CHARINDEX(',', String + ','), '')
        FROM Testdata
        UNION all

        SELECT
            OrderID,
            OrderName,
            LEFT(String, CHARINDEX(',', String + ',') - 1),
            STUFF(String, 1, CHARINDEX(',', String + ','), '')
        FROM tmp
        WHERE
            String > ''
    )

    SELECT
        OrderID,
        OrderName, 
    FROM tmp
    ORDER BY OrderID
Ravi Makwana
  • 2,782
  • 1
  • 29
  • 41
  • 2
    It won't - for starters, that's the slowest way possible to split strings in *unsupported* versions of SQL Server, In supported versions, `STRING_SPLIT` is available. Second, there's no order guarantee and the OP wants values matched by *element order*, not OrderID – Panagiotis Kanavos Jan 09 '20 at 07:52
0

You should split the strings and make union. I used approach for spliting string described here.

The code:

SELECT substring(OrderId, 1, CHARINDEX(',',OrderId)-1) OrderId,
       substring(OrderName, 1, CHARINDEX(':',OrderName)-1) OrderName
  FROM YourDataTable

UNION ALL

SELECT substring(OrderId, CHARINDEX(',',OrderId)+1, LEN(OrderId)),
   substring(OrderName, CHARINDEX(':',OrderName)+1, LEN(OrderName))
  FROM YourDataTable

This works only for the formatting in the sample data.

Marko Juvančič
  • 5,792
  • 1
  • 25
  • 41
0

If you have SQL Server 2016+, you may try an approach, based on JSON. Just transform the data into valid JSON arrays and parse these arrays with OPENJSON(). The result is a table with columns key, value and type, and the key column holds the index of the element in these arrays. With this approach you can parse more than two elements.

Table:

CREATE TABLE Data (
   OrderID nvarchar(100),    
   OrderName nvarchar(100)
)
INSERT INTO Data
   (OrderID, OrderName)
VALUES   
   ('11,12', 'A:B'),
   ('13,14', 'C:D'),
   ('15,16,17,18', 'E:F:G:H')

Statement:

SELECT j1.[value] AS OrderId, j2.[value] AS OrderName
FROM Data d
CROSS APPLY OPENJSON(CONCAT(N'[', d.OrderId, N']')) j1
CROSS APPLY OPENJSON(CONCAT(N'["', REPLACE(d.OrderName, N':', N'","'), N'"]')) j2
WHERE j1.[key] = j2.[key]

Result:

-----------------
OrderId OrderName
-----------------
11      A
12      B
13      C
14      D
15      E
16      F
17      G
18      H
Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • @DhruvRed Glad to help. If you think that this or any other answer is the best solution to your problem, you may accept it (only one answer can be accepted). – Zhorov Jan 14 '20 at 20:59