CODEWITHSUNDEEP
sql-servert-sql
WeIrDo
WeIrDo

Reputation: 23

Split comma separated values of two column by mapping them into new row using SQL

ID        NAME          COMMENTS
1          ABC           HI
2,3        DEF,GHI       HELLO
4          JKL           HII
5,6,7      M,N,O         HEY

I've the the above mentioned data in my table with comma separated values, these comma separated values are mapped sequentially. For Eg. id 1 is related to ABC, 2 is related to DEF and 3 is mapped to GHI and so on.

I want to use the sql query to get the below output:

ID        NAME      COMMENTS
1          ABC        HI
2          DEF        HELLO
3          GHI        HELLO
4          JKL        HII
5          M          HEY
6          N          HEY
7          O          HEY

Upvotes: 1

Views: 1966

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

If you have 4 or less delimited items you could split them using parsename. For 4 items or less this is the fastest method available:

Sample data and solution

-- Sample data
DECLARE @yourTable TABLE
(
  ID       varchar(100),
  Name     varchar(100),
  Comments varchar(100)
);

INSERT @yourTable VALUES 
  ('1','ABC','HI'),('2,3','DEF,GHI','HELLO'),('4','JKL','HII'),('5,6,7','M,N,O','HEY');

-- solution using parsename for splitting
SELECT 
  ID   = id_new,
  Name = name_new,
  Comments
FROM @yourTable
CROSS JOIN (VALUES (1),(2),(3),(4)) t(N)
CROSS APPLY 
(VALUES 
  (parsename(REPLACE(ID,',','.'), t.N), parsename(REPLACE(Name,',','.'), t.N))
) AS x(id_new, name_new)
WHERE id_new IS NOT NULL
ORDER BY id_new; -- for presentation only, not required and will slow you down

Results

ID   Name  Comments
---- ----- -----------
1    ABC   HI
2    DEF   HELLO
3    GHI   HELLO
4    JKL   HII
5    M     HEY
6    N     HEY
7    O     HEY

Execution Plan enter image description here

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

In SQL Server 2016+ you can use string_split().

Before SQL Server 2016, you need some other way to split strings. Using a CSV Splitter table valued function by Jeff Moden:

select 
    id = i.item
  , name = n.item
  , comments = t.comments
from t
  cross apply dbo.delimitedsplit8k(t.id,',') i
  cross apply dbo.delimitedsplit8k(t.name,',') n
where i.itemnumber = n.itemnumber

rextester demo: http://rextester.com/EEFRS3810

returns: 

+----+------+----------+
| id | name | comments |
+----+------+----------+
|  1 | ABC  | HI       |
|  2 | DEF  | HELLO    |
|  3 | GHI  | HELLO    |
|  4 | JKL  | HII      |
|  5 | M    | HEY      |
|  6 | N    | HEY      |
|  7 | O    | HEY      |
+----+------+----------+

splitting strings reference:

using John Cappelletti's in-line xml string splitter:

select 
    id = i.RetVal
  , name = n.RetVal
  , comments = t.comments
from t
   Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(t.[Id],',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as x
                Cross Apply x.nodes('x') AS B(i)
             ) i
   Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(t.[Name],',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as x
                Cross Apply x.nodes('x') AS B(i)
             ) n
where i.RetSeq = n.RetSeq

rextester demo: http://rextester.com/ZCKVNV72753

Upvotes: 5

Related Questions