CODEWITHSUNDEEP
sqlsql-server-2008
Shine
Shine

Reputation: 1423

how to split the column data to different rows

I have table Sitetable with a column that contains multiple values separated by comma (,) and would like to split it so I get each Site on its own row but with the same Number in front.

So my input data would be: 

Number             Site 
952240             2-78,2-89
952423             2-78,2-83,8-34

and my expected output would be:

Number             Site 
952240             2-78 
952240             2-89 
952423             2-78  
952423             2-83 
952423             8-34

Upvotes: 3

Views: 1696

Answers (4)

shakib
shakib

Reputation: 5469

This problem is already solved in: SQL Server 2008 - split multi-value column into rows with unique values

In your case,

With Inputs As
(
    select 952240 as Number, '2-78,2-89' as [Site]
    union 
    select 952423, '2-78,2-83,8-34'
)
, XCte As
(
    Select number, Cast( '<data>' + Replace( Inputs.[Site], ',', '</data><data>' ) + '</data>' As xml ) As XValue
    From Inputs
)
Select Number, Y.data.value('.','nvarchar(max)') As Site
From XCte
    Cross Apply XValue.nodes('//data') Y(data)

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

This one way to do what you want. Just replace @T with Sitetable in the query below.

declare @T table
(
  Number int,
  Site varchar(20)
)

insert into @T values 
(952240, '2-78,2-89'),
(952423, '2-78,2-83,8-34')

select T1.Number,
       T3.I.value('.', 'varchar(5)') as Site
from @T as T1
  cross apply (select cast('<i>'+replace(T1.Site, ',', '</i><i>')+'</i>' as xml)) as T2(X)
  cross apply T2.X.nodes('i') as T3(I)

Upvotes: 0

user330315
user330315

Reputation:

(Not directly the answer you were looking for, but as you asked for this in the comment...)

The reason why you need this "hack" is because your tables are not normalized. If you move the relation between site and number into its own table, your problem goes away:

 number_table (number, ...)
 site_table (site_id, site_name, ...)
 assigned_site (number, site_id)

With foreign keys from asssigned_site to number_table and site_table.

If your tables were structured that way, a simple join would do what you want:

 SELECT nb.number, agn.site_id, sit.site_name
 FROM number_table nb
    JOIN assigned_site agn ON nb.number = agn.number
    JOIN site sit ON sit.site_id = agn.site_id

The site_table is only necessary if your sites have additional attributes apart from the actual identifier (as in my example with the site_name).

If your site only consists of the ID then you don't need the site_table and your problem is reduced to a simple SELECT * FROM assigned_site.

Upvotes: 0

Talha Ahmed Khan
Talha Ahmed Khan

Reputation: 15433

There is a very good example of SplitStringToTable.

Upvotes: 1

Related Questions