Avoiding cursors, but...
-
My table has a column called sql_name. I need to concatenate all the values in this column and separate by comma and some other fluff.
Here are the values for the column:
A
A
B
I'm avoiding cursors with the following statement.
SELECT @strString1 = @strString1 + sql_name + ', '
FROM #temp ORDER BY SQL_name
This gives me a string with "A, A, B", but I need a distinct list - "A, B". Is it possible to do it with this statement or do I need to create another temp table with distinct values?
Thank you for replies
-
DO
SELECT @strString1 = @strString1 + sql_name + ', '
FROM (SELECT DISTINCT sql_name FROM #temp) A ORDER BY SQL_name
-
Use COALESCE. e.g.,
declare @IDList as varchar(8000)
SELECT @IDList = COALESCE(@IDList + ', ', '') +
CAST([ID] AS varchar(5))
FROM IDTable
SELECT @IDList
Happy programming
bm21 -
Antares686, Thank you.
Your solution works for me. I didn't know you can substitute the "select" statement for the table name. I tried to look for it in BOL, but to no avail.
Any idea where it might be listed?
-
Look in the index under subqueries or search for "Subquery Fundamentals"
-
It's called a "derived table". Search on that term. Select title, "using the FROM clause" in the location "Accessing and changing relational data".
[font="Courier New"]ZenDada[/font]
-
Thanks my brain is in other code right now.
-
And you can substitute fields with select statements like this. Say when you want the next value in a series - you can use a correlated subquery:
select * from #t order by 1
letter
------
a
b
c
d
e
select
letter,
(select min(u.letter) from #t u where u.letter > t.letter) nextletter
from #t t order by 1
letter nextletter
------ ----------
a b
b c
c d
d e
e NULL
[font="Courier New"]ZenDada[/font]
-
Wow! I looked through the article and found one more surprise - derived table can even be used in a join.
Thanks for your help!
-
No need for a derived table or COALESCE function:
SELECT DISTINCT @strString1 = @strString1 + sql_name + ', '
FROM #temp A ORDER BY SQL_name
Signature is NULL
-
I tried that and it didn't work. DId you test?
-
This is the first thing I tested. Didn't work for me.
-
Nope...didn't test it. You're right though, it doesn't work.
Very odd...
cl
Signature is NULL
-
Actually not odd when you think about it a little. The distinct is carried out after the concatenation is done. So you only get 1 result anyway.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Maybe this can help:
if exists (select * from sysobjects where id = object_id('dbo.fnc_10_parse_string') and xtype = 'TF')
drop function dbo.fnc_10_parse_string
GO
CREATE FUNCTION dbo.fnc_10_parse_string( @list varchar(8000))
RETURNS @tablevalues TABLE
( itemid int IDENTITY(1,1) , item varchar(8000) )
AS
BEGIN
declare @pos1 int
select @pos1 = 0
declare @startStringLen int
select @startStringLen = LEN(@list + '*') - 1
--This adding the '*' and then substracting 1 char is to get around the LEN
--issue of
--LEN
--Returns the number of characters, rather than the number of bytes, of the given string expression,
-- --->>>> excluding trailing blanks <<<< -----.
DECLARE @P_item varchar(255)
WHILE (@pos1 < @startStringLen)
BEGIN
select @pos1 = @pos1 + 1
SELECT @p_Item = SUBSTRING(@List,@pos1,1)
INSERT INTO @tablevalues
SELECT Item = @p_Item
END
RETURN
END
GO
Select distinct item from dbo.fnc_10_parse_string('aaaabcdefghijklmnopqrstuvwzyz')
SELECT * FROM
dbo.fnc_10_parse_string('aaaabcdefghijklmnopqrstuvwzyz')
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply