how to add description together
Jay
I have a query: (for example)
select description from description_table
return:
description
-------------
hello, world
hello, again
how can I write a query so that I can output just one row "hello, world
hello, again"
-Jay
Andrew J. Kelly
DECLARE @List VARCHAR(1000)
SELECT @List = @List + CASE WHEN @List = '' THEN '' ELSE ',' END + Desript
FROM YourTable
GROUP BY YourColumn
--
Andrew J. Kelly, SQL Server MVP
TargitInteractive
I proudly support the PASS SQL Server user community and its upcoming user
event,
PASS 2001 North America. For details, visit <http://www.sqlpass.org>
www.sqlpass.org.)
"Jay" <jc...@ibes.com> wrote in message news:e8MBLabIBHA.1348@tkmsftngp05...
Erland Sommarskog
Andrew J. Kelly (ake...@targitInteractive.com) writes:
> Try this:
>
> DECLARE @List VARCHAR(1000)
>
> SELECT @List = @List + CASE WHEN @List = '' THEN '' ELSE ',' END + Desript
> FROM YourTable
> GROUP BY YourColumn
Andrew,
First I suppose you mean ORDER BY and GROUP BY, because else the query
is not valid.
Second, the above technique may or may not work. People have posted
similar queries and only gotten the last row in the variable. There
is nothing documented in Books Online or elsewhere that guarantees
this to work.
So this is case where one is better off using an iterative technique,
even if it is less elegant. For instance in this case:
DECLARE my_cur INSENSITIVE CURSOR LOCAL FOR
SELECT descr
FROM tbl
ORDER BY descr_no
OPEN my_cur
SET @list = ''
WHILE 1 = 1
BEGIN
FETCH my_cur INTO @descr
IF @@fetch_status <> 0
BREAK
SET @list = CASE WHEN @list = '' THEN '' ELSE ',' END + @descr
END
DEALLOCATE my_cur
--
Erland Sommarskog, Abaris AB
som...@algonet.se
SQL Server MVP
Umachandar Jayachandran
question is what you are trying to do and whether you need to do this in
SQL. If not, it is better to do such operations in the client-side. here is
another solution using T-SQL:
DECLARE @Str varchar( 8000 )
SET @Str = ''
UPDATE description_table
SET @Str = @Str + description + ', '
WHERE ????
This technique is quite powerful and helpful for various problems. If
you want to the concatenating within several groups, then i suggest you
return the rows to the client side and do the processing.
--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )
Andrew J. Kelly
In this case I did mean to use an Order By and not the Group By, but a GROUP
BY could be used if the query were slightly different. As for this statement
not working at all or is valid I would have to disagree. I have been using
this technique for quite some time (several versions) and never had any
issues with it. If others can not get it to work I would like to see the
actual queries. I don't know if there is anything in BOL to say that this
will work but there isn't anything that states it won't either as far as I
know.
--
Andrew J. Kelly, SQL Server MVP
TargitInteractive
I proudly support the PASS SQL Server user community and its upcoming user
event,
PASS 2001 North America. For details, visit <http://www.sqlpass.org>
www.sqlpass.org.)
"Erland Sommarskog" <som...@algonet.se> wrote in message
news:Xns90FB95D1...@127.0.0.1...
Umachandar Jayachandran
declare @au_id varchar( 8000 )
set @au_id = ''
select @au_id = @au_id + a.au_id + ','
from pubs..authors as a
order by a.au_id + ','
print @au_id
I have posted examples similar to this one several times before. These
techniques (ordering with concatenation, newid in order by etc) are very
easy to break and if it works, it is by chance. You are just waiting for it
to hit you at some point. Personally, I have modified code that used these
techniques in SQL70/2000 because it didn't work on larger datasets or with
different indexes. I have done this way too many times when people used
solutions tied to the physical implementation or plan. Myself and BP have
always made it a point to warn about these but the occasional posts do get
by us :-(
>> I don't know if there is anything in BOL to say that this will work but
>> there isn't anything that states it won't either as far as I know.
Well, I just showed you one example where it breaks. Can you explain why
this shouldn't work? The reason is very simple, the end-result of the query
is all that matters and the SQL language doesn't tell you how it should be
done. We don't have to concern ourselves with how the plan is generated.
Dependency on a behavior is always sign for trouble. It will work for one
schema and not on the other. It will work for SQL2000/WIN2K but not on
SQL2000/NT40. The possibilities for failure is numerous.
Andrew J. Kelly
That's an eye opener for me. This is the first example I have seen that
didn't work. Now I am curious as to why the Order By breaks this and it
works fine without the Order By. Actually in most of the places I have used
this I don't use the Order By as I am just interested in building a
delimited list and the physical order of the data is not important. I guess
that's why I have never had any issues with it. Thanks Erland and UC.
--
Andrew J. Kelly, SQL Server MVP
"Umachandar Jayachandran" <umach...@yahoo.com> wrote in message
news:ehcgRGFJBHA.1840@tkmsftngp05...
Umachandar Jayachandran
I have seen cases where it fails without ORDER BY too! But that is for
complex queries on large data set. The point is that select when used like
that to initialize variables doesn't guarantee that the expression will be
calculated for every row. Here is what BOL says about assignment using
SELECT:
>>
SELECT @local_variable is usually used to return a single value into the
variable. It can return multiple values if, for example, expression is the
name of a column. If the SELECT statement returns more than one value, the
variable is assigned the last value returned.
>>
The last line to me says that we can't really rely on any iterative
behavior. It is very clear that the opitimizer or the MS SQL team/developer
can decide to initialize the variable with the last row's in whatever manner
they deem fit / efficient. Note that this doesn't talk anything about the
evaluation of expression that is assigned to the variable too! So therein
lies the problem.
SET is the better way to clearly indicate what you are doing in the code
in terms of assignment. If the SELECT returns multiple rows, then it will
raise an error. If you want to concatenate values, then a loop or the T-SQL
UPDATE extension is the safest approach.
Umachandar Jayachandran
>> works fine without the Order By.
Please see the query plan for the example with and without ORDER BY. The
output depends on when & how the expression is evaluated before the SORT,
you are stuck with the last row. If the expression is evaluated after the
SORT or at the end, then it is possible that the variable is initialized for
every row.
BP Margolin
Umachandar has pretty much covered all the points (as he usually does) ...
let me just add one point ... one that emphasizes the theory more than the
reality.
Joe Celko has posted a number of times that a SELECT query can not have an
ORDER BY clause because the output of a SELECT is a virtual table, and a
virtual table of course is an unordered set of rows. The ORDER BY clause, in
ANSI SQL, only really applies in the definition of a cursor, because a
cursor, originally way back when, was the means to "convert" sets (tables
with rows) to a form (files with records) that third generation languages
like COBOL and PL/I could handle. To use fancy words, the ORDER BY clause
covers the "impedance mismatch" between SQL (set-oriented) and 3G
(procedural) languages.
Because a SELECT doesn't have an ORDER BY clause, but a cursor does, the
ORDER BY clause is by necessity the **last** operation performed, and it is
performed on the result set of the SELECT.
SQL Server of course supports ORDER BY on SELECT, but still has to maintain
ANSI SQL compatibility as much as possible (even if just for marketing
reasons). The problem comes usually comes in that in order for some code to
be "meaningful", the ORDER BY clause really has to be executed ahead of when
it should be. Umachandar has posted one example, but the one I tend to like
is:
select seq = IDENTITY(int, 1, 1), *
into #t
from authors
order by au_lname, au_fname
It is everybody's expectation that the ORDER BY is going to be performed
before the INTO, but in a sense that violates ANSI SQL standards. Of course
it can be argued that since SELECT ... INTO ... is not ANSI SQL standard to
begin with, Microsoft has the freedom to define the behavior any way it
wants. However, to the best of my knowledge, Microsoft has never actually
documented that this is the way it will work. Microsoft has **implicitly**
done that because I believe that one of the fixes in SS2K SP1 was to
actually guarantee that the ORDER BY is done before the INTO.
But one of the points that I'm trying to make, and perhaps not very well, is
that unless ANSI SQL dictates a behavior, or Microsoft documents a behavior,
Microsoft does have the freedom to "break" the way things worked before. The
transition from SS 6.5 to SS 7.0 is the best illustration of this.
Developers were used to certain behavior ... GROUP BY implied an ORDER BY
... but Microsoft never documented or guaranteed that, and lo and behold in
SS 7.0, a GROUP BY no longer guaranteed an ORDER BY.
The other point that I'm trying to make is that it is really important to
have a thorough understanding of the ANSI SQL standard and its implications
in order to **know** where Microsoft is breaking the rules. If you know that
Microsoft is breaking the rules, then you also know that you should be
looking for documentation that guarantees the non-ANSI SQL compliant
behavior, and if it is not present, then you should be suspicious about why
it isn't there.
I, as usual, have rambled on ... hope some of it makes sense ... if not,
well sorry about that :-)
BPM
"Andrew J. Kelly" <ake...@targitmail.com> wrote in message
news:OG8wnBGJBHA.1840@tkmsftngp02...
Andrew J. Kelly
> SELECT @local_variable is usually used to return a single value into the
> variable. It can return multiple values if, for example, expression is the
> name of a column. If the SELECT statement returns more than one value, the
> variable is assigned the last value returned.
Again I want to thank you for opening my eyes about this behaviour and I
will certainly treat it differently from here on. But the statement above is
what made me think this would work in the first place. Let me explain. I
only use this on simple (single table selects with very few rows) and the
expression was always similar to this:
SELECT @x = @x + Column
and I previously assumed that that was handled differently than:
SELECT @x = Column
The second form explicitly sets the value of the column to the variable and
it is easy to see why the last row selected would typically be the value as
each one is like a new SET @x = y statement. Where as the first expression
appends the value of the column to the variable since the expression is
evualuated from left to right. So I always viewed these two as completely
different expressions and see no real difference between the first
expression and this expression that I see used all the time in code for
makeshift row numbers.
declare @x int
set @x = 1
select @x = @x + 1 from AnyTable
select @x
True this one uses a constant (1) instead of a column but the part about the
incrementing variable is the same. I can also see where a comlex query or an
Order BY may have an effect on this since the column values are not simply
fetched one by one any more but does anyone else see these two expressions
as different?
--
Andrew J. Kelly, SQL Server MVP
TargitInteractive
I proudly support the PASS SQL Server user community and its upcoming user
event,
PASS 2001 North America. For details, visit <http://www.sqlpass.org>
www.sqlpass.org.)
"Umachandar Jayachandran" <umach...@yahoo.com> wrote in message
news:OzqM7OGJBHA.1816@tkmsftngp02...
Umachandar Jayachandran
heritage really) uses the SELECT statement for assignment too. The SET
statement or the Oracle SELECT...INTO is much clearer since you know exactly
what will happen. The way I see it, a variable assignment is a singleton
operation. If you try to assign multiple values to the same variable, then
it should be a run-time error like it happens in SET (unfortunately MS SQL
doesn't do the same for SELECT assignment). So it just churns through the
rows and assigns the value to the variable arbitrarily. You still don't know
how and when the assignment happens. The only time I use SELECT for
assignment is if I want to do multiple assignments like:
SELECT @var1 = Col1, @var2 = Col2
FROM tbl
else I always used SELECT like (now I have switched to SET):
SELECT @var1 = ( SELECT col1 FROM tbl )
-- In sql70/2000
SET @var1 = ( SELECT col1 FROM tbl )
This will not give any unpredictable results and you don't have to worry
about any thing.
So going back to your example:
declare @x int
set @x = 1
select @x = @x + 1 from AnyTable
select @x
I would code it like below using the older assignment style:
declare @x int
set @x = 1
select @x = @x + ( select 1 from AnyTable )
-- or
select @x = ( select @x + 1 from AnyTable )
select @x
--
UC
Paul Thornett
news:ODE1wvGJBHA.1656@tkmsftngp03...
> select seq = IDENTITY(int, 1, 1), *
> into #t
> from authors
> order by au_lname, au_fname
>
> It is everybody's expectation that the ORDER BY is going to be
performed
> before the INTO, but in a sense that violates ANSI SQL standards.
Please would you expand on that last phrase beginning with "in a
sense...."?
--
Paul Thornett
Help fight Alzheimer's, Cystic Fibrosis and Mad Cow Disease
http://www.stanford.edu/group/pandegroup/Cosm/
Paul Thornett
> into #t
> from authors
> order by au_lname, au_fname
>
> It is everybody's expectation that the ORDER BY is going to be
performed
> before the INTO, but in a sense that violates ANSI SQL standards. Of
course
> it can be argued that since SELECT ... INTO ... is not ANSI SQL
standard to
> begin with, Microsoft has the freedom to define the behavior any way
it
> wants. However, to the best of my knowledge, Microsoft has never
actually
> documented that this is the way it will work. Microsoft has
**implicitly**
> done that because I believe that one of the fixes in SS2K SP1 was to
> actually guarantee that the ORDER BY is done before the INTO.
As far as I recall, the above SQL did not work under v7 in the same
way it now appears to work under v2000. May I presume that the way it
now works (to conform to "everybody's expectation") can be relied on
in the future?
Tibor Karaszi
> way it now appears to work under v2000. May I presume that the way it
> now works (to conform to "everybody's expectation") can be relied on
> in the future?
Only way to know that is if it is documented in BOL (or ć„Žossibly a KB, not sure about
that). If it is documented, we can hammer MS if it doesn't work. And MS would not
break documented behavior in next release without good reason.
If it isn't documented, all we can do is guess. Our contacts at MS might say that it
is safe, but what about the next project manager (or whatever) in MS dev team. Etc...
(Sorry to state the obvious, btw...).
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
"Paul Thornett" <pa...@idx.com.au> wrote in message
news:OnB1VHWJBHA.1320@tkmsftngp05...
BP Margolin
I don't have the full text that I posted, but I thought I did explain this,
at least to some extent, when I posted it. But perhaps I'm mistaken :-)
In ANSI SQL, the ORDER BY clause is the last thing done in a SELECT. The
"problem" is that since ANSI SQL doesn't support anything resembling the SQL
Server INTO clause, it leaves it open to Microsoft to determine whether the
ORDER BY executes before or after the INTO. Logically, at least to me, it
makes sense that the ORDER BY, in this case, is done before the INTO,
because otherwise it really makes no sense to add the ORDER BY clause at all
... however since Microsoft, to the best of my knowledge, has not stated
that this is the way that T-SQL will operate, there is no guarantee of this
behavior.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Paul Thornett" <pa...@idx.com.au> wrote in message
news:#Gi6YEWJBHA.1884@tkmsftngp02...
BP Margolin
To expand slightly on Tibor's post ...
If you go back to SQL Server 6.5, a GROUP BY was implemented by performing
an ORDER BY. Developers knew and depended upon this behavior. The problem
was that the ANSI SQL spec's do not require that a GROUP BY be implemented
this way, and in fact when SQL Server 7.0 came out, improvements in the
query optimizer supported alternative, and more efficient, ways to implement
a GROUP BY.
Applications that relied upon the internal knowledge of how SQL Server 6.5
operated "broke" when ported to SQL Server 7.0 (unless one ran it in 65
compatibility mode). Microsoft was extremely sympathetic, but bottom line
was that Microsoft said, "Point out in BOL or elsewhere where we make the
statement that one could depend upon a GROUP BY implying an ORDER BY. If you
can't find a statement by us on this, then you made an assumption that was
not valid. Sorry, but go change your code."
This is why many of us on these newsgroups are very wary of recommending
solutions that depend on convenient but undocumented features of SQL Server.
Either we have in the past been burned ourselves, or we have had to address
enough anguished cries from those who have been burned.
To, finally, answer your question ;-) :
To the best of my knowledge, Microsoft continues to be reluctant to
explicitly document this behavior. I know of existing bugs in SQL Server
that will not get fixed until Yukon because Microsoft does not want to break
existing code, but there is a very good chance that when Yukon comes out,
these bugs (and they are bugs because they violate ANSI SQL requirements)
will be fixed. Microsoft is aware of the implications of changing behavior
between versions, but it might make the behavior dependent upon the
compatibility level ... and I would expect that Microsoft would then
explicitly document the changes in behavior and base the behavior upon the
compatibility level.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Paul Thornett" <pa...@idx.com.au> wrote in message
news:OnB1VHWJBHA.1320@tkmsftngp05...