Normalise the below table in sql

开发者_如何学JAVAI 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.

---

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    

---

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