CONCATENATE Values in a group

can u give sample data with sample output?

What do you want to achieve?

I would like to concatenate all distinct row values returned by the query (Example above)  in to one linier row.

I tried to run the function and recevied this message

Server: Msg 170, Level 15, State 1, Procedure GetStockDesc, Line 1
Line 1: Incorrect syntax near 'max'.
Server: Msg 170, Level 15, State 1, Procedure GetStockDesc, Line 4
Line 4: Incorrect syntax near 'max'.
Server: Msg 137, Level 15, State 1, Procedure GetStockDesc, Line 8
Must declare the variable '@StockDesc'.
Server: Msg 137, Level 15, State 1, Procedure GetStockDesc, Line 8
Must declare the variable '@StockDesc'.
Server: Msg 137, Level 15, State 1, Procedure GetStockDesc, Line 18
Must declare the variable '@StockDesc'.

Server: Msg 170, Level 15, State 1, Procedure GetStockDesc, Line 1
Line 1: Incorrect syntax near 'max'.
Server: Msg 170, Level 15, State 1, Procedure GetStockDesc, Line 4
Line 4: Incorrect syntax near 'max'.
Server: Msg 137, Level 15, State 1, Procedure GetStockDesc, Line 8
Must declare the variable '@StockDesc'.
Server: Msg 137, Level 15, State 1, Procedure GetStockDesc, Line 8
Must declare the variable '@StockDesc'.
Server: Msg 137, Level 15, State 1, Procedure GetStockDesc, Line 18
Must declare the variable '@StockDesc'.

Select allOpen in new window

try this

create function GetStockDesc1(@StockNumber varchar(8000))
RETURNS varchar(8000) AS     
BEGIN    
DECLARE @StockDesc varchar(8000)
set  @StockDesc = ''

 
Select  @StockDesc = isnull(@StockDesc + ', ', '') + Stock.desc1
From    stock
Where  
  NOT(stock.units = 0 and stock.discont  = 1) 
and  RTRIM(stock.assoc) + '-' + LEFT(stock.number, 3) =  @StockNumber

 

  
  
RETURN  @StockDesc    
END

Select allOpen in new window

oops by mistake i renamed function to GetStockDesc1 please change it accordingly

Thanks,

it seems to work it is just taking a  over a minute to query to rows. Also I failed to mention inmy first post I need distinct values. any sugesstion on

Distinct Values
how to trim the spaces between commas
speed

sample:
, Emerica Wino Shoe (Black/White/Silver)                      , Emerica Wino Shoe (Black/White/Silver)                      , Emerica Wino Shoe (Black/White/Silver)  

try this

create function GetStockDesc(@StockNumber varchar(8000))
RETURNS varchar(8000) AS     
BEGIN    
DECLARE @StockDesc varchar(8000)
set  @StockDesc = ''

 
Select  @StockDesc = isnull(ltrim(rtrim(@StockDesc)) + ', ', '') + Stock.desc1
From    stock
Where  
  NOT(stock.units = 0 and stock.discont  = 1) 
and  RTRIM(stock.assoc) + '-' + LEFT(stock.number, 3) =  @StockNumber
group by Stock.desc1

if(LEN(@StockDesc) > 0)
set @StockDesc  = substring(LTRIM(rtrim(@StockDesc)), 2,LEN(@StockDesc))  

 

  
  
RETURN  @StockDesc    
END

Select allOpen in new window

The new function cleaned up the data with the exception of a leading space. I need to speed this up. I have changed it to look at a view and that helped speed dramatically however when the view selection goes from 50 to 350 records the query goes from 3sec to over a minute. any suggestions for speed? I have attached the code for the select, view and function and a image of the execution plan.  

select 

brandcat.number,
dbo.GetStockDesc (LTRIM(RTRIM(brandcat.number))) as keywords,
max(mfg) as mfg, 
max(prodtype) as prodtype,
SUM(availunitcount) as availunitcount,
SUM(InStock) as InStock,
max(MfgProdtype) as MfgProdtype,
MAX(ComparePrice) as ComparePrice,
MAX(Price) as Price,
MIN(MinPrice) as MinPrice,
avg(WholesalePrice) as WholesalePrice



from brandcat

group by brandcat.number

order by number

Select allOpen in new window

--create view brandcat as 

select

rtrim(stock.assoc)+'-'+left(stock.number,3) as Number,
stock.desc1,
max(mfg.mfg) as mfg, 
max(prodtype.search_desc) as prodtype,
(SUM(case when stock.units = 0 AND stock.discont = 0 then .5 when stock.units > 0 then 1 else stock.units end)) as availunitcount,
SUM(stock.units) as InStock,
max(mfg.mfg +' '+ prodtype.search_desc) as MfgProdtype,
MAX(stock.inetcprice) as ComparePrice,
MAX(stock.Price1) as Price,
MIN(stock.Price1) as MinPrice,
avg(sku_cost.unit_price) as WholesalePrice


from stock

join prodtype on stock.assoc=prodtype.prodtype
join mfg on left(stock.number,3)=num_code
join sku_cost on stock.number=sku_cost.number 

Where  NOT(stock.units = 0 and stock.discont  = 1) and stock.assoc='CLMTEE'

group by rtrim(stock.assoc)+'-'+left(stock.number,3), stock.desc1

order by rtrim(stock.assoc)+'-'+left(stock.number,3)

Select allOpen in new window

CREATE function GetStockDesc(@StockNumber varchar(8000))
RETURNS varchar(8000) AS     
BEGIN    
DECLARE @StockDesc varchar(8000)
set  @StockDesc = ''

 
Select  @StockDesc = isnull(rtrim(ltrim(@StockDesc)) + ', ', '') + brandcat.desc1
From    brandcat
Where  brandcat.number  =  @StockNumber


if(LEN(@StockDesc) > 0)
set @StockDesc  = substring(LTRIM(rtrim(@StockDesc)), 2,LEN(@StockDesc))  

 

  
RETURN  @StockDesc    
END

Select allOpen in new window

This is the best you can do with the function:

CREATE FUNCTION GetStockDesc (
			@StockNumber varchar(8000))

RETURNS varchar(8000)

AS 

BEGIN    
    DECLARE @StockDesc varchar(8000)
 
    SELECT  @StockDesc = ISNULL(@StockDesc + ', ', '') + LTRIM(brandcat.desc1)
    FROM    brandcat
    WHERE   brandcat.number = @StockNumber
  
    RETURN  @StockDesc    
END

Select allOpen in new window

Is there an index on brandcat.number?

This is probably the best you can do with the SELECT:

SELECT	number,
	dbo.GetStockDesc(number) AS keywords,
	MAX(mfg) AS mfg,
	MAX(prodtype) AS prodtype,
	SUM(availunitcount) AS availunitcount,
	SUM(InStock) AS InStock,
	MAX(MfgProdtype) AS MfgProdtype,
	MAX(ComparePrice) AS ComparePrice,
	MAX(Price) AS Price,
	MIN(MinPrice) AS MinPrice,
	AVG(WholesalePrice) AS WholesalePrice
FROM	brandcat
GROUP BY 
	number
ORDER BY
	number

Select allOpen in new window

No its a view can a index be put on a view? Do you think a table dump then index would help?

>>No its a view can a index be put on a view? <<
So brandcat is a VIEW?  Please post its contents.

I was using thw where clause stock.assoc = 'CLMTEE' because of the speed issue.

I will try and create a table and put a index on it.

Wow what a difference a index makes