Segregate column values in SQL Server

Question

I have a table with 2 columns (Col1 & Col2) and values are stores like below:

Col1          Col2
A/B/C         Red/Orange/Green
D/E           Red/Orange

I want the output like below.

Col1        Col2
A           Red
B           Orange
C           Green
D           Red
E           Orange

This is something which would be easier to handle outside of SQL Server. Consider fixing your data model before bringing it into the database. — Tim Biegeleisen, Jul 30 '20 at 09:34
This reminds me, why I like Postgres so much: `select t.* from the_table cross join unnest(string_to_array(col1, '/'), string_to_array(col2, '/')) as t(c1, c2)` (SCNR) — a_horse_with_no_name, Jul 30 '20 at 11:34
The linked *duplicate* is not correct in my eyes... This question is about splitting at least two values and match the items by their positions. The answers there are not really helpful with this... — Shnugo, Jul 30 '20 at 11:46
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. "I want" is not a question. — HABO, Jul 30 '20 at 13:12

score 0 · Answer 1 · edited Jul 30 '20 at 10:19

0

Not easy, but doable.

I would do it by "flattening" the table:

SELECT (left bit of column 1), (left bit of column2)

UNION ALL

SELECT (middle bit of column 1), (middle bit of column 2)
where [column 1] like '%/%'

UNION ALL

SELECT (last bit of column 1), (last bit of column 2)
where [column 1] like '%/%/%'

If you have the possibility of more slashes and data, you need to add further UNIONs.

Use CHARINDEX to find the slash and SUBSTRING to extract the bits.

edited Jul 30 '20 at 10:19

Dale K

21,987
13
41
69

answered Jul 30 '20 at 09:50

Lefty

392
1
12

for some there are 4 or 5 values also contain that is devided by / – Shubham Agarwal Jul 30 '20 at 10:10
Yes, just Copy and Paste the UNION/SELECT and make a couple of minor modifications - the principle stands, I have used it with 20 or 30 UNIONs, no problem. Not elegant, but it works. – Lefty Jul 30 '20 at 22:34

score 0 · Answer 2 · answered Jul 30 '20 at 09:52

0

Did you try CROSS APPLY? Please replace 'your_table_name' with the name of your table. It should work, just copy and paste.

   SELECT Col1, value AS Col2  INTO Table_2
    FROM your_table_name
    CROSS APPLY STRING_SPLIT(Col2, '/');  

   SELECT Col2, value AS Col1  INTO Table_3
    FROM Table_2
    CROSS APPLY STRING_SPLIT(Col1, '/'); 

   SELECT * FROM Table_3;

answered Jul 30 '20 at 09:52

Kokokoko

392
1
6
17

okay, I though that he/she wants the all values from Col2 corresponding to Col1. If it is about matching pairs then my solution is not suitable. – Kokokoko Jul 30 '20 at 09:59
1

From [`String_Split`](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15): "The output rows might be in any order. The order is _not_ guaranteed to match the order of the substrings in the input string." – HABO Jul 30 '20 at 12:13

score 0 · Answer 3 · answered Jul 30 '20 at 10:28

0

Maybe String split can help? https://docs.microsoft.com/it-it/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15 look at the example D and E

answered Jul 30 '20 at 10:28

Stefano Cucchi

42
4

1

From [`String_Split`](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15): "The output rows might be in any order. The order is _not_ guaranteed to match the order of the substrings in the input string." – HABO Jul 30 '20 at 12:13

score 0 · Answer 4 · answered Jul 30 '20 at 11:24

Unfortunately, the built-in string split function in SQL Server does NOT return the position in the string. In my opinion, this is a significant oversight.

Assuming your strings have no duplicate values, you can use row_number() and charindex() to add an enumeration:

select t.*, ss.*
from t cross apply
     (select s1.value as value1, s2.value as value2
      from (select s1.value,
                   row_number() over (order by charindex('/' + s1.value + '/', '/' + t.col1 + '/')) as pos
            from string_split(t.col1, '/') s1
           ) s1 join
           (select s2.value,
                   row_number() over (order by charindex('/' + s2.value + '/', '/' + t.col2 + '/')) as pos
            from string_split(t.col2, '/') s2
           ) s2
           on s1.pos = s2.pos
     ) ss;

Here is a db<>fiddle.

Segregate column values in SQL Server

4 Answers4