subquery inside select to concatenate results ?

14 Jul 2006 9:50 PM

mdscorp

this code works fine

DECLARE @st varchar(100)
SELECT @st=@st+service.status FROM service WHERE service.id=@ID

SELECT @st will display all data in the status field just concatenated

Now, i wonder if i can do this

SELECT a,b,c,(SELECT @st=@st+service.status FROM service WHERE
service.id=@ID) FROM xTable

this should result in displaying all data in fields a,b and c plus the
concatenation of the data in service.status field...

but I get a syntax error near '='

some expert mayhave some work around ?

thanks

14 Jul 2006 10:12 PM

Erland Sommarskog

mdscorp (l***@mds-corp.com) writes:

> this code works fine
>
> DECLARE @st varchar(100)
> SELECT @st=@st+service.status FROM service WHERE service.id=@ID

First observation: as you have written the code, @st will always be
NULL. (Unless you have set CONCAT_NULL_YIELDS_NULL off, which is a
very bad idea.)

Aparat from that, it only looks as if it works fine. The correct result from
this type of query is not defined. You may get what you want, or you
may get something else (Most likely only one value.)

If you are on SQL 2000, you have no choice but to iterate over the
table to get the desired result. On SQL 2005 you can use FOR XML PATH.

> Now, i wonder if i can do this
>
> SELECT a,b,c,(SELECT @st=@st+service.status FROM service WHERE
> service.id=@ID) FROM xTable

It follows from the above, that this question is moot. But apart from
that, you cannot do variable assignment in subqueries.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

15 Jul 2006 6:15 AM

cn.popeye

declare @st varchar(100)
declare @s table(aa char(1),vv char(1))
set @st=''
insert into @s
select 1,1
union all
select 2,2
union all
select 3,3
SELECT @st=@st+aa FROM @s
SELECT aa,vv, @st
FROM @s

"mdscorp" <l***@mds-corp.com>

??????:1152913821.349407.261***@p79g2000cwp.googlegroups.com...
Show quote

> this code works fine
>
> DECLARE @st varchar(100)
> SELECT @st=@st+service.status FROM service WHERE service.id=@ID
>
> SELECT @st will display all data in the status field just concatenated
>
> Now, i wonder if i can do this
>
> SELECT a,b,c,(SELECT @st=@st+service.status FROM service WHERE
> service.id=@ID) FROM xTable
>
> this should result in displaying all data in fields a,b and c plus the
> concatenation of the data in service.status field...
>
> but I get a syntax error near '='
>
> some expert mayhave some work around ?
>
> thanks
>

15 Jul 2006 6:15 AM

cn.popeye

declare @st varchar(100)
declare @s table(aa char(1),vv char(1))
set @st=''
insert into @s
select 1,1
union all
select 2,2
union all
select 3,3
SELECT @st=@st+aa FROM @s
SELECT aa,vv, @st
FROM @s

"mdscorp" <l***@mds-corp.com>

??????:1152913821.349407.261***@p79g2000cwp.googlegroups.com...
Show quote

> this code works fine
>
> DECLARE @st varchar(100)
> SELECT @st=@st+service.status FROM service WHERE service.id=@ID
>
> SELECT @st will display all data in the status field just concatenated
>
> Now, i wonder if i can do this
>
> SELECT a,b,c,(SELECT @st=@st+service.status FROM service WHERE
> service.id=@ID) FROM xTable
>
> this should result in displaying all data in fields a,b and c plus the
> concatenation of the data in service.status field...
>
> but I get a syntax error near '='
>
> some expert mayhave some work around ?
>
> thanks
>

15 Jul 2006 8:06 AM

Omnibuzz

You may want to check this link
http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/