How to concatenate strings from a column into a single row (with NULL value)?

15 Sep 2006 9:07 AM

Cylix

<snip>
CREATE TABLE Colors
(
    Color VARCHAR(32)
)
GO

SET NOCOUNT ON
INSERT Colors SELECT 'red'
INSERT Colors SELECT 'orange'
INSERT Colors SELECT 'blue'
INSERT Colors SELECT 'green'
GO

DECLARE @colors VARCHAR(1024)

SELECT
    @colors = COALESCE(@colors + ',', '') + Color
FROM
    Colors

SELECT Colors = @colors
GO

DROP TABLE Colors
GO
</snip>

<TSQL do>
Color
------
red
orange
blue
green

TO

Colors
-------------------------
red,orange,blue,green
</TSQL do>

However, It doesn't handle the cell value is NULL, any advise?

Thank you.

15 Sep 2006 9:31 AM

Simon Sabin

Hello Cylix,

You need to handle the NULL in the concatenation
i.e
SELECT @colors = COALESCE(@colors + ',', '') + ISNULL(Color,'<NULL>') --change
<NULL> to what ever you want
FROM Colors

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

Show quoteHide quote

> <snip>
> CREATE TABLE Colors
> (
> Color VARCHAR(32)
> )
> GO
>
> SET NOCOUNT ON
> INSERT Colors SELECT 'red'
> INSERT Colors SELECT 'orange'
> INSERT Colors SELECT 'blue'
> INSERT Colors SELECT 'green'
> GO
> DECLARE @colors VARCHAR(1024)
>
> SELECT
> @colors = COALESCE(@colors + ',', '') + Color
> FROM
> Colors
> SELECT Colors = @colors
> GO
> DROP TABLE Colors
> GO
> </snip>
> <TSQL do>
> Color
> ------
> red
> orange
> blue
> green
> TO
>
> Colors
> -------------------------
> red,orange,blue,green
> </TSQL do>
> However, It doesn't handle the cell value is NULL, any advise?
>
> Thank you.
>

15 Sep 2006 9:57 AM

Damien

Simon Sabin wrote:
Show quoteHide quote

> Cylix wrote:
> > <snip>
> > CREATE TABLE Colors
> > (
> > Color VARCHAR(32)
> > )
> > GO
> >
> > SET NOCOUNT ON
> > INSERT Colors SELECT 'red'
> > INSERT Colors SELECT 'orange'
> > INSERT Colors SELECT 'blue'
> > INSERT Colors SELECT 'green'
> > GO
> > DECLARE @colors VARCHAR(1024)
> >
> > SELECT
> > @colors = COALESCE(@colors + ',', '') + Color
> > FROM
> > Colors
> > SELECT Colors = @colors
> > GO
> > DROP TABLE Colors
> > GO
> > </snip>
> > <TSQL do>
> > Color
> > ------
> > red
> > orange
> > blue
> > green
> > TO
> >
> > Colors
> > -------------------------
> > red,orange,blue,green
> > </TSQL do>
> > However, It doesn't handle the cell value is NULL, any advise?
> >
> > Thank you.
> >
> Hello Cylix,
>
> You need to handle the NULL in the concatenation
> i.e
> SELECT @colors = COALESCE(@colors + ',', '') + ISNULL(Color,'<NULL>') --change
> <NULL> to what ever you want
> FROM Colors
>
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>

You also need to be aware that this behaviour (having a variable
re-assigned for each row in the set) does not appear in SQL Server 2005
(I believe you'll have the variable assigned with only one of the
values)

Damien

15 Sep 2006 9:47 AM

ML

Be careful with that method. More details here:
http://milambda.blogspot.com/2005/07/return-related-values-as-array.html

(blogger is having some poblems at the moment)

ML

---
http://milambda.blogspot.com/

15 Sep 2006 11:28 AM

Aaron Bertrand [SQL Server MVP]

> However, It doesn't handle the cell value is NULL, any advise?

Why bother pulling a NULLvalue at all?

WHERE color IS NOT NULL

15 Sep 2006 11:40 AM

ML

Maybe null = transparent? :)

ML

---
http://milambda.blogspot.com/

15 Sep 2006 3:56 PM

Arnie Rowland

Wouldn't it be nice if there where 'sticky' posts -you know, questions that
are asked everyday and could always be at the top for those unable to figure
out how to scroll down and look at previous questions...

Just thinking aloud here...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quoteHide quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:elopZnL2GHA.3576@TK2MSFTNGP03.phx.gbl...
>> However, It doesn't handle the cell value is NULL, any advise?
>
> Why bother pulling a NULLvalue at all?
>
> WHERE color IS NOT NULL
>

15 Sep 2006 4:02 PM

Aaron Bertrand [SQL Server MVP]

> Wouldn't it be nice if there where 'sticky' posts -you know, questions
> that are asked everyday and could always be at the top for those unable to
> figure out how to scroll down and look at previous questions...
>
> Just thinking aloud here...

That's what FAQ web sites, and google, are for.

However, as with those two technologies, "sticky" posts would be ignored for
the most part also.

A

15 Sep 2006 1:25 PM

Anith Sen

See if this helps:
http://www.projectdmx.com/tsql/rowconcatenate.aspx

--
Anith