Normalise the below table in sql

Question

I have below table. which has more then one name

Id  AllNames
1   A,B,C
2   A,B
3   X,Y,Z

I want to display data in the below normalised way.

Id  Names
1   A
1   B
1   C
2   A
2   B
3   X
3   Y
3   Z

Could any one help me to solve it.

Thanks in advance.

You want a query to split the Allnames? Or you want the correct table design? — gbn, Jul 27 '11 at 10:08
Split names would be enough. Going forward i can change my table structure. — Shine, Jul 27 '11 at 11:34
See this post http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor — Cyril Gandon, Jul 27 '11 at 11:50

JStead · Answer 1 · 2011-07-27T12:04:07.147

First you will need one of the one million sql server split functions that can be found on the internet.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

CREATE FUNCTION dbo.Split
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Then you will need a cursor or something to loop through each row. Split the column then insert into the table of your choice.

Declare @denorm table (
id int,
val varchar(50)
)
Declare @denormCol varchar(max),@originalId int    

declare stackCursor CURSOR LOCAL FAST_FORWARD FOR
select id,allText
from yourTable

FETCH NEXT FROM stackCursor 
INTO  @denormCol, 
    @originalId 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      insert into @denorm
      Select @originalId,Data
      from dbo.Split(@denormCol,',')
  END
CLOSE stackCursor 
DEALLOCATE stackCursor

JStead · Answer 2 · 2011-07-27T12:09:48.940

Just because I love options one other way you could do this is with a cte I haven't seen it done this way but it makes sense to me. As a side not I don't have sql server with me if you run into max recursion you might have to add 1 to the start of the substring in the second all names case/

    with recCTE as (
    select id,substring(allNames,0,charindex(',',allNames)) name,substring(allNames,charindex(',',allNames),len(allNames)-charindex(',',allNames)) allNames
    from yourTable
    union all
    select id,
    case when charindex(',',allNames) >0 then
    substring(allNames,0,charindex(',',allNames)) name
    else 
    allNames name
    end
    ,case when charindex(',',allNames) >0 then
     substring(allNames,charindex(',',allNames),len(allNames)-charindex(',',allNames)) allNames
    else 
     ''
    end 
    from recCTE 
    where allNames <> ''
)

select id,name
from recCTE

Normalise the below table in sql

2 Answers2