<div>
can u give sample data with sample output?
</div>

concat-example.jpg

<div>
What do you want to achieve?
</div>

<div>
I would like to concatenate all distinct row values returned by the query (Example above) &nbsp;in to one linier row. 

</div>

<div>
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'. 
 

<pre><code class="code-10 nolinks" id="code-8167749">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'.
</code></pre>
</div>

<div>
try this 

<pre><code class="code-10 nolinks" id="code-8167751">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
</code></pre>
</div>

<div>
oops by mistake i renamed function to GetStockDesc1 please change it accordingly 
</div>

<div>
Thanks, 
 
it seems to work it is just taking a &nbsp;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) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, Emerica Wino Shoe (Black/White/Silver) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, Emerica Wino Shoe (Black/White/Silver) &nbsp; 
</div>

<div>
try this 

<pre><code class="code-10 nolinks" id="code-8167761">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) &gt; 0)
set @StockDesc = substring(LTRIM(rtrim(@StockDesc)), 2,LEN(@StockDesc)) 

 

 
 
RETURN @StockDesc 
END
</code></pre>
</div>

<div>
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. &nbsp;
<pre><code class="code-10 nolinks" id="code-8168220">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
</code></pre>
 
<pre><code class="code-10 nolinks" id="code-8168222">--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 &gt; 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)
</code></pre>
 
<pre><code class="code-10 nolinks" id="code-8168223">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) &gt; 0)
set @StockDesc = substring(LTRIM(rtrim(@StockDesc)), 2,LEN(@StockDesc)) 

 

 
RETURN @StockDesc 
END
</code></pre>
 <a href="https://filedb.experts-exchange.com/incoming/2011/05_w21/459514/ex-plan.jpg" target="_blank" title="Execution Plan (120 KB)"><img alt="Execution Plan" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2011/05_w21/800_459514/ex-plan.jpg" /></a>
</div>

ex-plan.jpg

<div>
This is the best you can do with the function: 

<pre><code class="code-10 nolinks" id="code-8168283">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
</code></pre>
</div>

<div>
Is there an index on brandcat.number? 
 
This is probably the best you can do with the SELECT: 

<pre><code class="code-10 nolinks" id="code-8168287">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
</code></pre>
</div>

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

<div>
&gt;&gt;No its a view can a index be put on a view? &lt;&lt; 
So brandcat is a VIEW? &nbsp;Please post its contents.
</div>

<div>
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.
</div>

BrandCat.jpg

<div>
Wow what a difference a index makes
</div>

<div>
<div class="content wysiwyg-content">
I have seen many articles on concatenating data when grouping but I lack knowledge of functions and am wonder if someone can help me with a select or shed some light on a function necessary to to concatenate a field when grouping. 
 
I am using SQLSERVER 2000 

<pre><code class="code-10 nolinks" id="code-8167669">select

MAX('M-'+parentstock.style) as Number,
case when charindex('(', parentstock.title) &gt; 0 then rtrim(LEFT(parentstock.title, charindex('(',parentstock.title )-1)) else parentstock.title end as MasterStyle,

--CONCATENATE with coma sperators parentstock.title here

from parentstock

group by case when charindex('(', parentstock.title) &gt; 0 then rtrim(LEFT(parentstock.title, charindex('(',parentstock.title )-1)) else parentstock.title end
</code></pre>
</div>
</div>

I have seen many articles on concatenating data when grouping but I lack knowledge of functions and am wonder if someone can help me with a select or shed some light on a function necessary to to concatenate a field when grouping.

I am using SQLSERVER 2000

CONCATENATE Values in a group

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.