Split comma separated value from table column into rows using mssql?

Question

I would like to get some data into mssql view by splitting their sources. I have some columns where phone numbers are stored as comma separated values (each contains a phone contact). I neet to work with each "phone contact", so I would like to see them in rows each one. And also each row has to contain an order of the contact from the splitting.

Source:

Department   | SaleMngrs   | Operators      | Secretary
----------------------------------------------------------
'Technics'   | '123,456,77'| '+122,Line 1'  | '77889,112'
'Development'| '123,3366'  | null           | 'Lines 7-8'

As you can see, the comma separated values are a total mess, but the spliter is , (comma).

Wanted result:

Department   | TypeOfContact  | Contact    | ContactOrder
------------------------------------------------------
'Technics'   | 'SalesManagers'| '123'      | 1
'Technics'   | 'SalesManagers'| '456'      | 2
'Technics'   | 'SalesManagers'| '77'       | 3
'Technics'   | 'Operators'    | '+122'     | 1
'Technics'   | 'Operators'    | 'Line 1'   | 2
'Technics'   | 'Secretary'    | '77889'    | 1
'Technics'   | 'Secretary'    | '112'      | 2
'Development'| 'SalesManagers'| '123'      | 1
'Development'| 'SalesManagers'| '3366'     | 2
'Development'| 'Secretary'    | 'Lines 7-8'| 1

No UDF or SP wanted. Just a SELECT please.

You are not going to be able to do that in a single select statement. — Sean Lange, Aug 28 '14 at 16:32
TypeOfContact is there. The second column is SalesManagers followed by Operators and Secretaries. — Sean Lange, Aug 28 '14 at 16:38
@Sean Lange I dont understand what you mean by your second comment... — procma, Aug 28 '14 at 16:47
hehe. Somebody else deleted their comment. They asked where TypeOfContact was in your data. Seems pretty bizarre without that comment there anymore. — Sean Lange, Aug 28 '14 at 16:51
possible duplicate of [Turning a Comma Separated string into individual rows](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) — Code Different, Aug 28 '14 at 17:35

Wagner DosAnjos · Accepted Answer · 2014-08-29T12:17:45.983

Please try the following (it is as pretty as the data structure) - optimized with UNPIVOT:

set nocount on

declare @source table (Department varchar(50), SaleMngrs varchar(50), Operators varchar(50), Secretary varchar(50));

insert into @source values ('Technics'   , '123,456,77', '+122,Line 1'  , '77889,112');
insert into @source values ('Development', '123,3366'  , null           , 'Lines 7-8');

;WITH cte (Department, TypeOfContact, Contact)
AS
(
   SELECT Department, TypeOfContact, cast('<Contact><c>' + replace(Contact,',','</c><c>') + '</c></Contact>' as xml) AS Contact
     FROM (SELECT Department, SaleMngrs AS SalesManagers, Operators, Secretary FROM @source) p
     UNPIVOT (Contact FOR TypeOfContact IN (SalesManagers, Operators, Secretary)) AS unpvt
)
Select Department
     , TypeOfContact
     , Contact.c.value('.','varchar(20)') AS Contact
     , ROW_NUMBER() OVER (PARTITION BY Department, TypeOfContact ORDER BY Department, TypeOfContact) AS ContactOrder
FROM cte CROSS APPLY Contact.nodes('/Contact/c') as Contact(c);

OUTPUT

Department   TypeOfContact Contact              ContactOrder
------------ ------------- -------------------- --------------------
Development  SalesManagers 123                  1
Development  SalesManagers 3366                 2
Development  Secretary     Lines 7-8            1
Technics     Operators     +122                 1
Technics     Operators     Line 1               2
Technics     SalesManagers 123                  1
Technics     SalesManagers 456                  2
Technics     SalesManagers 77                   3
Technics     Secretary     112                  1
Technics     Secretary     77889                2

EDIT: Optimized query using UNPIVOT (original below):

set nocount on

declare @source table (Department varchar(50), SaleMngrs varchar(50), Operators varchar(50), Secretary varchar(50));

insert into @source values ('Technics'   , '123,456,77', '+122,Line 1'  , '77889,112');
insert into @source values ('Development', '123,3366'  , null           , 'Lines 7-8');

;WITH cte (Department, SalesMngrs, Operators, Secretary)
AS
(
   select Department
        , cast('<SaleMngrs><c>' + replace(SaleMngrs,',','</c><c>') + '</c></SaleMngrs>' as xml) AS SalesMngrs
        , cast('<Operators><c>' + replace(Operators,',','</c><c>') + '</c></Operators>' as xml) AS Operators
        , cast('<Secretary><c>' + replace(Secretary,',','</c><c>') + '</c></Secretary>' as xml) AS Secretary
     from @source
)
Select Department
     , TypeOfContact
     , Contact
     , ROW_NUMBER() OVER (PARTITION BY Department, TypeOfContact ORDER BY Department, TypeOfContact) AS ContactOrder
FROM (
   Select Department, 'SalesManagers' AS TypeOfContact, SaleMngrs.c.value('.','varchar(20)') as Contact
   from cte CROSS APPLY SalesMngrs.nodes('/SaleMngrs/c') as SaleMngrs(c)
   union
   Select Department, 'Operators', Operators.c.value('.','varchar(20)')
   from cte CROSS APPLY Operators.nodes('/Operators/c') as Operators(c)
   union
   Select Department, 'Secretary', Secretary.c.value('.','varchar(20)')
   from cte CROSS APPLY Secretary.nodes('/Secretary/c') as Secretary(c)
) AS q;

I think it's possible to use UNPIVOT to optimize this query. — Wagner DosAnjos, Aug 29 '14 at 11:36

score 0 · Answer 2 · answered Aug 28 '14 at 17:42

Martin,

I know you wanted this in one SQL Statement, but if you create a Function then the SQL won't be so unpretty.

ALTER FUNCTION dbo.Split (  @InputString  VARCHAR(8000),  @Delimiter VARCHAR(50))  
RETURNS @Items TABLE ( Item VARCHAR(8000), Rowid INT)  
AS  
BEGIN  
      IF @Delimiter = ' '  
      BEGIN  
            SET @Delimiter = ','  
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)  
      END  

      IF (@Delimiter IS NULL OR @Delimiter = '')  
            SET @Delimiter = ','  

      DECLARE @Item                 VARCHAR(8000)  
      DECLARE @ItemList       VARCHAR(8000)  
      DECLARE @DelimIndex     INT  
      declare @rowseq         INT

      SET @rowseq = 0

      SET @ItemList = @InputString  
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)  
      WHILE (@DelimIndex != 0)  
      BEGIN  
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            SET @rowseq = @rowseq + 1  
            INSERT INTO @Items VALUES (@Item, @rowseq)  

            -- Set @ItemList = @ItemList minus one less item  
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)  
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)  
      END -- End WHILE  

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString  
      BEGIN  
            SET @Item = @ItemList  
            SET @rowseq = @rowseq + 1
            INSERT INTO @Items VALUES (@Item, @rowseq)  
      END  

      -- No delimiters were encountered in @InputString, so just return @InputString  
      ELSE INSERT INTO @Items VALUES (@InputString, 1)  

      RETURN  

END

The above Function I found from this SO question, but i made some changes to it for your scenario. How to split a comma-separated value to rows

Then you SQL will be...

SELECT department, 'SalesManager' as TypeOfContract, s.Item as Contact , s.rowId 
FROM <YOUR TABLE> t 
     CROSS APPLY 
       (SELECT * FROM dbo.Split(t.SalesMngrs, ',') where item is not null) S
UNION ALL 
SELECT department, 'Operators' as TypeOfContract, s.Item as Contact , s.rowId  
FROM <YOUR TABLE> t

     CROSS APPLY 
       (SELECT * FROM dbo.Split(t.Operators, ',') where item is not null ) S   
UNION ALL
SELECT department, 'Secretary' as TypeOfContract, s.Item as Contact , s.rowId  
FROM <YOUR TABLE> t 
     CROSS APPLY 
       (SELECT * FROM dbo.Split(t.Secretary, ',') where item is not null) S

I hope this helps.

Thanx @Ed Mendez, have you tried the performance? Is it better or worse than one-select written above by @wdosanjos? — procma, Aug 29 '14 at 07:50

Split comma separated value from table column into rows using mssql?

2 Answers2