locked
field with multiple commas RRS feed

  • Question

  • Hi ,

     

    I have table with 2 fields

    ID and Data field

    like

    ID    DATA

    1    aaa,bbb,ccc,ddd

    2   eee,ffff,yyy

    3  uuuu

    4 iiii,oooo

    How can I make them

    multiple records like

    ID     DATA

    1      aaaa

    1      bbbb

    so on

    thnx,

    Gok

    Monday, June 28, 2010 6:17 PM

Answers

All replies

  • You can create an inline table-valued function to split the list and return a row per each element in the list, and call the function using the "apply" operator.

    Convert CSV values in three columns to rows

    Arrays and Lists in SQL Server

    AMB

    • Proposed as answer by Naomi N Monday, June 28, 2010 6:28 PM
    • Marked as answer by Gok Sky Tuesday, June 29, 2010 12:43 PM
    Monday, June 28, 2010 6:25 PM
  • Something like that:

     

    create FUNCTION dbo.[ParseCsvList](@List varchar(8000))
    RETURNS @IDList TABLE 
    (
    	Value varchar(255)
    )
    AS
    BEGIN
    	if (@List is null or Len(@List) = 0)
    		RETURN 
    
    	if (substring(@List,Len(@List),1) <> ',')
    		select @List = @List + ','
    
    	;WITH CTE(F, L)
    	AS
    	(
    		select 1, charindex(',',@List)
    		union all
    		select L + 1, charindex(',',@List,L + 1)
    		from CTE
    		where charindex(',',@List,L + 1) <> 0
    	)
    	insert into @IDList(Value)
    		select substring(@List,F,L-F)
    		from CTE
    	OPTION (MAXRECURSION 0);
    	
      RETURN
    END
    go
    
    declare @T table(Id int, value varchar(255))
    
    insert into @T(Id, value)
    	select 1, '1'
    	union all
    	select 2, '20,21,abc'
    	union all
    	select 3, '30,31,def'
    	
    select t.Id, p.value
    from	
    	@T t cross apply dbo.[ParseCsvList](t.value) p
    
    	

    Monday, June 28, 2010 6:25 PM
  • Here is a recent thread on a similar issue

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8d08d2c1-93e9-4331-8456-3ddd26686aea


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, June 28, 2010 6:29 PM
  • One way I do it is to create a function and the use CROSS APPLY, as you see below

    CREATE FUNCTION [dbo].[CommaSeparatedList]
    (
     @cslist VARCHAR(8000)
    )
    RETURNS @t TABLE
    (
     Item VARCHAR(64)
    )
    BEGIN 
     DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000) 
     
     WHILE @cslist <> '' 
     BEGIN 
     SET @spot = CHARINDEX(',', @cslist) 
     IF @spot>0 
      BEGIN 
      SET @str = LEFT(@cslist, @spot-1) 
      SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot) 
      END 
     ELSE 
      BEGIN 
      SET @str = @cslist 
      SET @cslist = '' 
      END 
     INSERT @t SELECT @str
     END 
     RETURN
    END
    
    
    
    
    
    DECLARE @t TABLE(ID INT,DATA VARCHAR(100))
    INSERT INTO @t
    SELECT 1,'aaa,bbb,ccc,ddd' UNION 
    SELECT 2,'eee,ffff,yyy'		UNION
    SELECT 3,'uuuu'				UNION
    SELECT 4,'iiii,oooo'
    
    SELECT ID,P.Item
    FROM @t t
    CROSS APPLY [dbo].[CommaSeparatedList](t.Data) AS P

    Abdallah El-Chal, PMP, ITIL, MCTS
    Monday, June 28, 2010 6:30 PM