By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,576 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,576 IT Pros & Developers. It's quick & easy.

Select several values into a single variable

P: n/a
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.

May 11 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Are you sure that actually does something? I didn't think that would
work...

May 11 '06 #2

P: n/a
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

May 11 '06 #3

P: n/a
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
May 11 '06 #4

P: n/a
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
May 11 '06 #5

P: n/a
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


May 11 '06 #6

P: n/a
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
May 11 '06 #7

P: n/a
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.

May 12 '06 #8

P: n/a
> 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

May 12 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.