SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


get all values separated by commas


get all values separated by commas

Author
Message
lfcost
lfcost
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 65
Comments posted to this topic are about the item get all values separated by commas
brian.healy
brian.healy
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 177
Neat use of update. This can also be done using the FOR XML construct:

select SUBSTRING((SELECT (',' + id) FROM teste ORDER BY id FOR XML PATH('') ), 2, 1000)
lfcost
lfcost
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 65
nice, but lookout to the substring length, as i have to lookout for the nvarchar max length.
Arnold Lieberman
Arnold Lieberman
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 482
stuff((select ',' + ID
from teste
for XML path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'
),
1,
1,
'')
This copes with any string length.
JayJag
JayJag
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 19
Hi-

How about something like the following. It may have some bugs but can be used as a starting point.

drop procedure sp_splitIntoIndividualComponents
go

create procedure sp_splitIntoIndividualComponents
As
declare @str varchar(200)
set @str = '2,3,4,5,6,7,8,9,'

;With t1(n1, str1, str2, n)
as
(
select 1, convert(varchar(200), (substring(@str, 0, charIndex(',', @str)))) str1,
convert(varchar(200), (substring(@str, charIndex(',', @str) + 1, 20))) str2,
charIndex(',', @str) as n
union all
select 2, convert(varchar(200), (substring(t1.str2, 0, charIndex(',', t1.str2)))) str1,
convert(varchar(200), (substring(t1.str2, charIndex(',', t1.str2) + 1, 20))) str2,
charIndex(',', t1.str2) as n
from t1 where CHARINDEX(',', t1.str2) > 0
union all
select 3, convert(varchar(200), t1.str2) str1,
'' str2,
charIndex(',', t1.str2) as n
from t1 where CHARINDEX(',', t1.str2) = 0 and (LEN(t1.str1) > 0 Or LEN(t1.str2) > 0)
)
select t1.str1 into #t from t1 where t1.str1 <> ''
select * from #t
go

exec sp_splitIntoIndividualComponents
go
Arnold Lieberman
Arnold Lieberman
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 482
Problem with recursive CTEs is that they are very slow in comparison to the XML method.

Please see here for a much better article on the subject.
lfcost
lfcost
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 65
Arnold Lieberman (7/19/2012)
stuff((select ',' + ID
from teste
for XML path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'
),
1,
1,
'')
This copes with any string length.


ok... i loved the for XML path.
The stuff it's kind of cute, but i didn't understand TYPE... and the ./text thing... I have to study more.
Where can I learn more about this stuff?

I only have the 2778 MS Quering SQL server 2005 lessons's in the bag, and a little experience.
Thanks
Arnold Lieberman
Arnold Lieberman
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 482

ok... i loved the for XML path.
The stuff it's kind of cute, but i didn't understand TYPE... and the ./text thing... I have to study more.
Where can I learn more about this stuff?

I only have the 2778 MS Quering SQL server 2005 lessons's in the bag, and a little experience.
Thanks


Well, there's a fantastic website called SQL Server Central which I learned a lot from, and continue to do so!
Most programmers who come from the usual programming languages have a hard time using tsql in the best manner. Declarative programming requires a completely different mindset to solving problems. My general rule of thumb is that if you have to use a loop or a cursor then you're doing it the wrong way :-D
Steven Willis
Steven Willis
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3957 Visits: 1721
Fewer steps and converts nulls to blanks if you want them (take out the ISNULL operator if you don't want null values). You can also easily make the delimiter a defined variable.



--Create a test table
CREATE TABLE dbo.Test (testval VARCHAR(10))
GO

--Insert values for testing
INSERT INTO dbo.Test (testval) VALUES ('T1')
INSERT INTO dbo.Test (testval) VALUES ('T2')
INSERT INTO dbo.Test (testval) VALUES (NULL)
INSERT INTO dbo.Test (testval) VALUES ('T3')
GO


DECLARE @i NVARCHAR(MAX)

SELECT @i = COALESCE(@i + ',','') + CAST((ISNULL(testval,'')) AS NVARCHAR(MAX))
FROM dbo.Test

SELECT @i



JayJag
JayJag
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 19
This is a great idea.

Thanks for posting.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search