How to get multiple row from single row for data separated by some character on single select statement

Question

How to get unique multiple rows from single row for data separated by some character on single select query without split function in SQL Server 2008 or above.

Example :

ID  Data                                                Name
1   '2014-01-01,2014-01-02,2014-01-03,2014-01-04'       'A1'
2   '2014-01-01,2014-01-02'                             'B1'
3   '2014-01-03,2014-01-05,2014-01-06,2014-01-07'       'A1'


ID  Data            Name
1   '2014-01-01'    'A1'
1   '2014-01-02'    'A1'
1   '2014-01-03'    'A1'
1   '2014-01-04'    'A1'
3   '2014-01-05'    'A1'
3   '2014-01-06'    'A1'
3   '2014-01-07'    'A1'
2   '2014-01-01'    'B1'
2   '2014-01-02'    'B1'

It may help [How to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Maryam Arshi, Feb 20 '14 at 16:52

score 0 · Accepted Answer · answered Feb 20 '14 at 17:29

Check out this SQL Sentry article on string splitting. If goes over the different techniques, speed of each one and any pitfalls.

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

Split the string and take a distinct values.

The code below uses the xml spliter.

-- Just playing
use tempdb;
go

-- drop the table
if object_id('results') > 0 
drop table results
go

-- create the table
create table results
(
  id int,
  data varchar(128),
  name varchar(2)
);
go

-- add data
insert into results values
(1,'2014-01-01,2014-01-02,2014-01-03,2014-01-04','A1'),
(2,'2014-01-01,2014-01-02','B1'),
(3,'2014-01-03,2014-01-05,2014-01-06,2014-01-07','A1');
go

-- Just the data
select * from results

-- Split the data
select distinct r.id, r.name, s.item 
from results r 
cross apply dbo.SplitStrings_XML(data, ',') s

enter image description here

How to get multiple row from single row for data separated by some character on single select statement

1 Answers1