SQL many values statement

[iminore]

Suppose I have a table of companyids and orderids:
companyid 3 orderid 4
5 6
3 7
3 9

Is there a single SQL statement that will return all the orderids as a delimited string for companyid 3, say, without looping through a select min() statement?

 

[donutman]

Declare @String varchar(1000)
Select @String=colease(@String+', ',str(OrderID)) 
   from MyTable where CompanyID=3

-Karl


[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[iminore]

I suspect there's a misspelling in there somewhere.

colease ?

 

[john76]

coalesce

 

[iminore]

well,

Declare @String varchar(1000)
Select @String=coalesce(@String+', ',str(OrderID))
from MyTable where CompanyID=3

returns 4, which is what I'd expect from coalesce

I'm after 4,7,9,

????????

 

[donutman]

As of this morning the drugs must not have worn off.
The correct code is:

Declare @String varchar(1000)
Select @String=coalesce(@String+', ','') + str(OrderID)
   from MyTable where CompanyID=3

-Karl


[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[iminore]

yep, it works - wonderful

any chance of an explanation?

 

[donutman]

The coalesce just gets rid of the leading ', ' that you would get if you did this instead.

Declare @String varchar(1000)
Set @String=''
Select @String=@String + str(OrderID)
   from MyTable where CompanyID=3

Notice that with this version you have to initialize @String=''. Otherwise it would be NULL and NULL + 'something' = NULL. With the coalesce, the 1st time @String is used (remember it's NULL), the 2nd argument is used instead...and that is a blank string.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[iminore]

yeh, forget the coalesce, the truth is I didn't really understand the power of the 'select' statement. I suspect this applies to many experienced programmers. I also suspect this is a bit of a hobbyhorse of yours.
Oh go on - what's the best book/website you know on SQL?

 

[donutman]

I don't think there is a best book. There are so many and each has its strengths. Really, I'm not being difficult...just go to Boarders and look through a bunch of them. Over time, your opinion on which is the best (at the time) will change as you gain experience.
The best web site, well this one of course. Haven't found a better one FOR SQL.
A month ago I didn't know about "that" power of the Select either. I learned it here. Yes it is my hobby right now, but that changes every few months.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]