convert srting col1,col2,col3 into rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

convert srting col1,col2,col3 into rows

Author

Topic

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-10-06 : 04:29:01

hi I have rocrd in particular table T as

ID name
1 pradeep, Vinay

My output should look like

pradeep
vinay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 04:34:21

use parsename function

SELECT t.ID,f.Val
FROm YourTable t
CROSS APPLY dbo.ParseValues(t.name)f

parsevalues can be found below

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(100) 
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 04:40:19

example


declare @test table
(
ID int,
name varchar(8000)
)
insert into @test
select 1, 'pradeep,Vinay' union all
select 2, 'Sanjay,Moti,Manu' union all
select 3, 'Kiran' union all
select 4, 'Rahul,Parveen,Harilal' union all
select 5, 'Mohan,Ram,Rita,Mary,Sita'


select t.id,f.val
from @test t
cross apply dbo.parsevalues(t.name) f 

output
-------------------------------------------------------
id          name
----------- ---------------------------------------------------------
1           pradeep
1           Vinay
2           Sanjay
2           Moti
2           Manu
3           Kiran
4           Rahul
4           Parveen
4           Harilal
5           Mohan
5           Ram
5           Rita
5           Mary
5           Sita

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-06 : 07:17:12

We can use this method also

declare @test table
(
ID int,
name varchar(8000)
)
insert into @test
select 1, 'pradeep,Vinay' union all
select 2, 'Sanjay,Moti,Manu' union all
select 3, 'Kiran' union all
select 4, 'Rahul,Parveen,Harilal' union all
select 5, 'Mohan,Ram,Rita,Mary,Sita'

SELECT
t.id,
SUBSTRING(t.name, v.Number - 1, COALESCE(NULLIF(CHARINDEX(',', t.name, v.Number), 0), LEN(t.name) + 1) - v.Number + 1) AS name
FROM @test AS t
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(',_' + t.name, v.Number, 1) = ','

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources