Link to home
Start Free TrialLog in
Avatar of ubsmail
ubsmail

asked on

CONCATENATE Values in a group

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
select

MAX('M-'+parentstock.style) as Number,
case when charindex('(', parentstock.title) > 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) > 0 then rtrim(LEFT(parentstock.title, charindex('(',parentstock.title )-1)) else parentstock.title end

Open in new window

Avatar of anillucky31
anillucky31
Flag of India image

can u give sample data with sample output?
Avatar of ubsmail
ubsmail

ASKER

User generated image
Avatar of Sharath S
What do you want to achieve?
Avatar of ubsmail

ASKER

I would like to concatenate all distinct row values returned by the query (Example above)  in to one linier row.
ASKER CERTIFIED SOLUTION
Avatar of anillucky31
anillucky31
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ubsmail

ASKER

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

Open 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

Open in new window

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

ASKER

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

Open in new window

Avatar of ubsmail

ASKER

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

Open 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)

Open 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

Open in new window

User generated image
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

Open 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

Open in new window

Avatar of ubsmail

ASKER

No its a view can a index be put on a view? Do you think a table dump then index would help?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>No its a view can a index be put on a view? <<
So brandcat is a VIEW?  Please post its contents.
Avatar of ubsmail

ASKER

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.
Avatar of ubsmail

ASKER

User generated image
Avatar of ubsmail

ASKER

Wow what a difference a index makes