Spilting values in rows. - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Spilting values in rows.

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-09 : 23:18:42

Hi guys,
I have a table like this.

Declare @tbl as table(Invoiceid int,Dc varchar(100))

Insert into @tbl
Select 1,'101,102,104'union all
Select 2,'105,106,107' union all
Select 3,'110,111,112,113,114' union all
Select 4 ,'115'

I want to split the values in the column Dc.The output should be

Invoiceid Dc
1--------- 101
1--------- 102
1--------- 104
2--------- 105
2--------- 106
2--------- 107
... and so on.
Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 00:07:14

Create a function like this:-

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val int  
)  
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

and use it like this

Declare @tbl as table(Invoiceid int,Dc varchar(100))

Insert into @tbl
Select 1,'101,102,104'union all
Select 2,'105,106,107' union all
Select 3,'110,111,112,113,114' union all
Select 4 ,'115'


SELECT t.InvoiceId,b.Val FROM @tbl t
CROSS APPLY ParseValues(t.Dc)b

Output
------------------------------------------------
InvoiceId   Val
----------- -----------
1           101
1           102
1           104
2           105
2           106
2           107
3           110
3           111
3           112
3           113
3           114
4           115

ranganath
Posting Yak Master

209 Posts

Posted - 2008-06-10 : 01:40:01

Hi,

try with this also

Declare @tbl as table(Invoiceid int,Dc varchar(100))

Insert into @tbl
Select 1,'101,102,104'union all
Select 2,'105,106,107' union all
Select 3,'110,111,112,113,114' union all
Select 4 ,'115'

DECLARE @delimiter NVARCHAR(5)
SELECT @delimiter = ','

SELECT s.Invoiceid,
SUBSTRING(s.DC, v.Number - 1,
COALESCE(NULLIF(CHARINDEX(',', s.DC, v.Number), 0), LEN(s.DC) + 1) - v.Number + 1) AS value
FROM @tbl AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(',_' + s.DC, v.Number, 1) = ','

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-10 : 05:32:04

Thank you guys for your help & feedback.
I ended up having this solution.Most part of it is inspired by visakhs solution.

CREATE FUNCTION ParseValues
(@String varchar(8000)
)
RETURNS @RESULTS TABLE
(
Val int
)
AS
BEGIN

DECLARE @x Xml
SET @x='<i>'+REPLACE(@String,',','</i><i>')+'</i>'
INSERT INTO @RESULTS (Val)
SELECT x.i.value('.','int') FROM @x.nodes('//i')x(i)
RETURN
END

Declare @tbl as table(Invoiceid int,Dc varchar(100))

Insert into @tbl
Select 1,'101,102,104'union all
Select 2,'105,106,107' union all
Select 3,'110,111,112,113,114' union all
Select 4 ,'115'

SELECT t.InvoiceId,b.Val FROM @tbl t
CROSS APPLY ParseValues(t.Dc)b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources