Results from 1 column of many records into comma list
-
Hello,
I am looking to do a select of the contents of one column from possibly many records updating the results into one column/one row of another table.
Table1:
Column - Auto contains the following records
"Chevy"
"Ford"
"Dodge"
"Toyota"
I would like to select these records from Table1 and update a record in Table2 column AllAutos = "Chevy, Ford, Dodge, Toyota"
Is there an easy non-cursor way to do this?
Thanks,
Dan
-
Hi Dan
You can try using a recursive UDF. You will pass to it the last car's name, as well as the string of already-concatenated names. I have tried it with the following:
CREATE FUNCTION dbo.AppendCarNames
(@LastName VarChar(50),
@Concatenation VarChar(8000))
RETURNS VarChar(8000)
AS
BEGIN
DECLARE @Result VarChar(8000)
DECLARE @CarName VarChar(50)
SET @Result = @Concatenation
SET @CarName = (SELECT TOP 1 CarName FROM CarTable WHERE CarName > @LastName ORDER BY CarName ASC)
IF @CarName IS NOT NULL
BEGIN
SET @Result = @Result + RTRIM(@CarName) + ', '
SET @Result = dbo.AppendCarNames(@CarName, @Result)
END
RETURN ( @Result )
END
The first time you call it, you must just pass two empty strings, ensuring that the parameters are not null initially.
Hope it helps.
Schalk Lubbe
-
Another way would be:
SET NOCOUNT ON
CREATE TABLE note
(
col1 VARCHAR(8)
)
INSERT INTO note (col1) VALUES('Notes1')
INSERT INTO note (col1) VALUES('Notes2')
INSERT INTO note (col1) VALUES('Notes3')
INSERT INTO note (col1) VALUES('Notes4')
INSERT INTO note (col1) VALUES('Notes5')
INSERT INTO note (col1) VALUES('Notes6')
INSERT INTO note (col1) VALUES('Notes7')
INSERT INTO note (col1) VALUES('Notes8')
INSERT INTO note (col1) VALUES('Notes9')
INSERT INTO note (col1) VALUES('Notes10')
DECLARE @allnotes VARCHAR(8000)
SELECT
@allnotes = ISNULL( @allnotes + ', ', '' ) + col1
FROM
note
SELECT
@allnotes
SET NOCOUNT OFF
-------------------------------------------------------------------------------
Notes1, Notes2, Notes3, Notes4, Notes5, Notes6, Notes7, Notes8, Notes9, Notes10
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Shalk and Frank!
Your suggestions were great. I went with a modified version of Franks example.
Dan
-
DECLARE @sList VARCHAR(8000)
SELECT @sList = COALESCE(@sList + ',','') + SomeField FROM SomeTable
-
haha, my method is clearly described in the earlier post. Sorry.
-
Regarding...
ISNULL( @allnotes + ', ', '' ) vs. COALESCE(@sList + ',','')
Shouldn't ISNULL be preferred here rather than COALESCE, since there is only 1 possible NULL argument value?
Is the peformance difference worthy of mention? Or is this just nit-picking?
I know for me, I used COALESCE all over the place until I realized ISNULL was a better fit.
Greg
-
There are subtle differences between COALESCE and ISNULL. Consider this:
CREATE TABLE #t
(
c1 CHAR
)
INSERT INTO #t VALUES (NULL)
SELECT
ISNULL(c1,'Frank')
, COALESCE(c1,'Frank')
FROM
#t
SELECT ISNULL(c1,'Frank')
, COALESCE(c1,'Frank')
,CASE WHEN c1 IS NULL THEN 'Frank' ELSE c1 END
FROM
#t
DROP TABLE #t
SELECT
7 / ISNULL(CAST(NULL AS int), 2.00)
, 7 / COALESCE(CAST(NULL AS int), 2.00)
, 7 / CASE WHEN CAST(NULL AS int) IS NULL THEN 2.00 END
When you run these,
But then have a look at this:
select
coalesce((
select
a2.au_id
from
pubs..authors a2
where
a2.au_id = a1.au_id ),'')
from
pubs..authors a1
select
isnull((
select
a2.au_id
from
pubs..authors a2
where
a2.au_id = a1.au_id ),'')
from
pubs..authors a1
Now, compare here the execution plans.
Fazit: I tend to use COALESCE only then when I need the extended functionality. Usually I use ISNULL.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yeah, I would use ISNULL instead of COALESCE in this case, but be aware of frank's above post producing the different results.
I noticed something interesting. You can delcare the variable
@sList as VARCHAR
instead of
@sList VARCHAR (8000)
but @sList as VARCHAR does not work. It runs with no errors, but @sList is empty. I am wondering why this is so?
-
@sList VARCHAR without any dimensioning is equivalent to @sList VARCHAR(1).
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply