Select several values into a single variable

[Malc]

Can somebody please tell me whether the following syntax is supported
or whether it's a "feature" that will someday stop working. It works in
both SQL Server 2000 and 2005 at the moment.

declare @var varchar(1000)
set @var = ''
select @var = @var + colx from some_table where col1 = some_value

colx is a varchar or at least is cast to one as part of the select
statement. If the where clause would normally return more than one row,
all returned values for colx are concatenated into @var.
I've not seen this syntax before but that doesn't make it wrong ;-)

Malc.

[figital]

Are you sure that actually does something? I didn't think that would
work...

[Malc]

See for yourself...

use pubs
go

declare @var varchar(1000)
set @var = ''
select @var = @var + ' ' + pub_id from pub_info
where 1 = 1
order by
pub_id desc
print @var

[Erland Sommarskog]

Malc (M.*********@massey.ac.nz) writes:

Can somebody please tell me whether the following syntax is supported
or whether it's a "feature" that will someday stop working. It works in
both SQL Server 2000 and 2005 at the moment.

declare @var varchar(1000)
set @var = ''
select @var = @var + colx from some_table where col1 = some_value

The correct result of this operation is undefined. You may get a
concatenated list, or only a single value.

I recommend against using this.

There is a supported way to do this on SQL 2005 with help of FOR XML PATH.
Unfortunately, I have the sample code at home only, so I cannot post it.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Thu, 11 May 2006 12:35:11 +0000 (UTC), Erland Sommarskog wrote:

Malc (M.*********@massey.ac.nz) writes:

Can somebody please tell me whether the following syntax is supported
or whether it's a "feature" that will someday stop working. It works in
both SQL Server 2000 and 2005 at the moment.

declare @var varchar(1000)
set @var = ''
select @var = @var + colx from some_table where col1 = some_value

The correct result of this operation is undefined. You may get a
concatenated list, or only a single value.

I recommend against using this.

There is a supported way to do this on SQL 2005 with help of FOR XML PATH.
Unfortunately, I have the sample code at home only, so I cannot post it.

Hi Erland and Malc,

Tony Rogerson featured the FOR XML PATH method of concatenating in his
blog entry of today:
http://sqlserverfaq.com/blogs/blogs/...05/11/429.aspx

--
Hugo Kornelis, SQL Server MVP

[Amb]

I was the person that originally showed this method to Malc. THis was
used at a company I worked at previously. After talking with my old
work mates, I recieved this information from the guy who first came up
with it: Ill quote it direct from an MSN chat log with the names
changed.

[MSN Log]
Me: I dont suppose you have any opinion on this <URL to this newsgroup>
Me: I suggested it as an answer to a problem for our DBA and now he
wants to know if its a hack fix or predictable known SQL server
behaviour
GN: Just a sec will have a look
GN: Its not a hack. It is designed this way. They talked about it
Tech-Ed.
GN: Eg: To get the balance in a banking app.
create table Test
(
Client varchar(10),
Bal money
)
GO
insert into test values ('1',10)
GO
declare @bal money
declare @addition money
select @addition = 100
update test set @bal = bal = bal + @addition
select @bal
[/end]

The above example is clearly more advanced than the first, but it
works. The thing is that if it isnt supported, then why is it being
taught at the Tech Ed conferences? GN went on to specify that the
method as shown above in Malc's post was taught as a way of doing a
single field pivot.
Erland Sommarskog wrote:

Malc (M.*********@massey.ac.nz) writes:

Can somebody please tell me whether the following syntax is supported
or whether it's a "feature" that will someday stop working. It works in
both SQL Server 2000 and 2005 at the moment.

declare @var varchar(1000)
set @var = ''
select @var = @var + colx from some_table where col1 = some_value

The correct result of this operation is undefined. You may get a
concatenated list, or only a single value.

I recommend against using this.

There is a supported way to do this on SQL 2005 with help of FOR XML PATH.
Unfortunately, I have the sample code at home only, so I cannot post it.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Amb (as*************@hotmail.com) writes:

GN: Eg: To get the balance in a banking app.
create table Test
(
Client varchar(10),
Bal money
)
GO
insert into test values ('1',10)
GO
declare @bal money
declare @addition money
select @addition = 100
update test set @bal = bal = bal + @addition
select @bal
[/end]

The above example is clearly more advanced than the first, but it
works. The thing is that if it isnt supported, then why is it being
taught at the Tech Ed conferences? GN went on to specify that the
method as shown above in Malc's post was taught as a way of doing a
single field pivot.

But this example is different from what Malc asked about. Here @bal
appears only to the left. So this example should be fine.

It's when you have

SELECT @x = @x + col

or

UPDATE tbl
SET @x = col = col + @x

you cannot be sure what will happen.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

It is a kludge that is known, but not supported. The results are
random, based on the physical order of the data in storage.

Programmers that do not know what 1NF is or who want to violation a
tiered architecture by formatting in the database or who like to get
job security by proprietary programming will use it.

[Alexander Kuznetsov]

> The results are random, based on the physical order of the data in storage.

The same is true about OLAP functions, the results are random, based on
the physical order of the data in storage:

create table t(i char(1), c char(1))
Table created

insert into t(i,c) values('1','A')
1 row inserted
insert into t(i,c) values('1','B')
1 row inserted
select i, c,row_number() over(order by i) rn from t
I C RN
- - ----------
1 A 1
1 B 2
2 rows selected
delete from t
2 rows deleted
insert into t(i,c) values('1','B')
1 row inserted
insert into t(i,c) values('1','A')
1 row inserted
select i, c,row_number() over(order by i) rn from t
I C RN
- - ----------
1 B 1
1 A 2
2 rows selected
drop table t
Table dropped