Comma Separated in SQL

Question

I have a table tblexmaple with following fields

FieldOne       Field Two        FieldThree
----------------------------------------------
1              Test1            1,2,3
2              Test2            2,3,4,5
3              Test3            1

Result want is

1      Test1     1
1      Test1     2
1      Test1     3
2      Test2     2
2      Test2     3
2      Test2     4
2      Test2     5
3      Test3     1

Can any body Please help?

You didn't describe your problem, nor what you are trying to accomplish. See http://stackoverflow.com/help/how-to-ask and http://stackoverflow.com/help/quality-standards-error — parvus, Dec 16 '13 at 05:58
http://stackoverflow.com/questions/3936088/mysql-split-comma-separated-list-into-multiple-rows — Steven Wexler, Dec 16 '13 at 05:59
@parvus I think it's pretty clear that OP wants to normalize his data by translating FieldThree into multiple rows froma a comma separated list. — Steven Wexler, Dec 16 '13 at 06:00
You can use PLSQL to get it, I dont think its possible to write a query which can do this — varun, Dec 16 '13 at 06:03
You should **never** store relational data this way. What you want is a separate table for `FieldThree` values and a [junction table](http://en.wikipedia.org/wiki/Junction_table) to handle the *many-to-many* relationship — Phil, Dec 16 '13 at 06:07
You can check this post http://stackoverflow.com/questions/5096584/how-to-expand-comma-separated-field-into-multiple-rows-in-mysql — Zo Has, Dec 16 '13 at 06:10
Your solution for storing foreign keys is mentioned as an anti pattern in [SQL Anti-Patterns](http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557). — Amir Pashazadeh, Dec 16 '13 at 06:24

score 0 · Answer 1 · edited Dec 16 '13 at 07:36

Below is solution for you issue. Replace Table_Test with you tblexmaple. and there is on function used in below query, i have also put the code for that function.

Query :-

DECLARE @Table TABLE
(
  ID INT,
  Name VARCHAR(100)
)

DECLARE @ResultTable TABLE
(
  FieldOne INT,
  FieldTwo VARCHAR(100),
  FieldThree INT
)

INSERT INTO @Table
SELECT 
    ID,
    Name
FROM Table_Test 

DECLARE @ID INT;
DECLARE @Name VARCHAR(100);
DECLARE @IDs VARCHAR(100);
WHILE (SELECT COUNT(*) FROM @Table) > 0
BEGIN
    SELECT TOP 1 @ID = ID,@Name = Name FROM @Table
    SELECT @IDs = IDs FROM Table_Test WHERE ID = @ID

    INSERT INTO @ResultTable
    SELECT @ID, @Name, * FROM dbo.Split(@IDs,',')

    DELETE FROM @Table WHERE ID = @ID   
END

SELECT * FROM @ResultTable

Function Code :-

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 12/16/2013 12:15:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(500))       
as       
begin       
    declare @idx int       
    declare @slice varchar(MAX)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end  
GO

Comma Separated in SQL

1 Answers1