Concatenation Failure

[briangriffin]

I've read the FAQ and other posts regarding concatenating fields into a long string, but something isn't quite right when I try. Using the AdventureWorks db, I want a listing of each sales territory and then one field listing each salesperson in that territory:

declare @v_sales table

	(territoryID int ,
	territory varchar(15),
	salesmen varchar(255))
	

insert into @v_sales (territoryID,territory, salesmen)
select TerritoryID, Name,''
from  Sales.SalesTerritory
order by 1



declare @v_salesmen table
	(territoryID int,
	salesperson varchar(40),
	sales numeric(20,2))

insert into @v_salesmen
select     
c.TerritoryID,
d.FirstName + ' ' + d.LastName, 
a.SalesYTD
from Sales.SalesPerson a INNER JOIN HumanResources.Employee b
	on b.EmployeeID = a.SalesPersonID 
LEFT OUTER JOIN Sales.SalesTerritory c 
	on c.TerritoryID = a.TerritoryID 
INNER JOIN Person.Contact AS d 
	on d.ContactID = b.ContactID
order by 1 

Declare @min int, @max int
Select @min = 1, @max = Max(TerritoryID)
From @v_sales


While @min <= @max
 Begin
   Update @v_sales
     Set salesmen = salesmen + b.salesperson + ' '
   From @v_sales a  inner join @v_salesmen b
     ON a.territoryID = b.territoryID
   Where a.territoryID = @min

   Select @min = @min + 1
 End

select * from @v_sales

I'm getting a list of territories, but only one salesman from each - where did I go wrong? (I'm not concerned with the sales total field right now).

Thanks.

 

[PGO01]

I think you're going about it the wrong way - it should be far simpler by grouping. Here's a simple example:

SELECT
    TerritoryID,
    FirstName,
    LastName
FROM
    Sales.SalesPerson
GROUP BY
    TerritoryID,
    FirstName,
    LastName

Or if you really want to concatenate the SalesPerson records into 1 string then you could do it more efficiently with a self join.

 

[gmmastros]

It's hard to say, but I think your problem is concerning nulls. When you concatenate a null to a string, the result is NULL. Try this:

   Update @v_sales
     Set salesmen = salesmen + b.salesperson + ' '
   From @v_sales a  inner join @v_salesmen b
     ON a.territoryID = b.territoryID
   Where a.territoryID = @min
[!]         And b.salesperson is not null[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[briangriffin]

Thanks to you both - I think the problem was related to the nulls. I ended up using the coalesce function in 2005:

declare @v_salesmen table
	(territoryID int,
	salesperson varchar(40))

insert into @v_salesmen
select     
case when c.TerritoryID is null then 0 else c.TerritoryID end,
d.FirstName + ' ' + d.LastName
from Sales.SalesPerson a inner join HumanResources.Employee b
	on b.EmployeeID = a.SalesPersonID 
left outer join Sales.SalesTerritory c 
	on c.TerritoryID = a.TerritoryID 
inner join Person.Contact AS d 
	on d.ContactID = b.ContactID
order by 1 


declare @v_counter int, @v_max int
select @v_counter = min(territoryID) from @v_salesmen
select @v_max = max(territoryID) from @v_salesmen

declare @v_salestable table 
				(territoryID int,
				salesmen varchar(1000))

while @v_counter <= @v_max
BEGIN
declare @v_saleslist varchar(8000)
set @v_saleslist = ''
select @v_saleslist = coalesce(@v_saleslist + ', ','') + salesperson
from @v_salesmen
where territoryID = @v_counter

insert into @v_salestable(territoryID,salesmen) values (@v_counter, right(@v_saleslist,len(@v_saleslist)-1))

set @v_counter = @v_counter + 1

END

select * from @v_salestable

 

[gmmastros]

Either way is fine. I prefer to filter the NULLs instead. Why bother coalescing when you don't have to.

Anyway... I'm glad you got it to work.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Just a comment that you can also use the isnull function so

case when c.TerritoryID is null then 0 else c.TerritoryID end

would be

isnull(c.TerritoryID, 0)

Not that your way is wrong just an FYI

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!