CONCATENATE Values in a group

ubsmail
ubsmail used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
anillucky31

Commented:
can u give sample data with sample output?
ubsmail

Author

Commented:
Example
SharathData Engineer

Commented:
What do you want to achieve?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Attend Free Webinar
ubsmail

Author

Commented:
I would like to concatenate all distinct row values returned by the query (Example above)  in to one linier row.
Commented:
First create the attached code as function

then use it in your select statment as like this after wholesaleprice.

please correct the column name in function according to your need

 max(dbo.GetStockDesc(RTRIM(stock.assoc) + '-' + LEFT(stock.number, 3))) as stockdesc


create function GetStockDesc(@StockNumber nvarchar(max))
RETURNS varchar(max) AS     
BEGIN    
DECLARE @StockDesc varchar(max)
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

ubsmail

Author

Commented:
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

anillucky31

Commented:
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

anillucky31

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

Author

Commented:
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)  
anillucky31

Commented:
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

ubsmail

Author

Commented:
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

Execution Plan
Anthony Perkins
Top Expert 2012

Commented:
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

Anthony Perkins
Top Expert 2012

Commented:
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

ubsmail

Author

Commented:
No its a view can a index be put on a view? Do you think a table dump then index would help?
Anthony Perkins
Top Expert 2012
Commented:
The VIEW should be restructured so that you do not have to use a GROUP BY on it.  At face value there is little point in the GROUP BY on stock.assoc if the only value is going to be "CLMTEE".
Have you got an index on the following columns:
      stock.assoc
      prodtype.prodtype
      mfg.num_code
      sku_cost.number
      stock.number
Can you create a computed column on LEFT(stock.number, 3) and index it? Otherwise you are going to be condemned to a table scan.
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) = mfg.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 
	stock.assoc, 
	LEFT(stock.number, 3),
	stock.desc1
ORDER BY
	stock.assoc,
	LEFT(stock.number, 3)

Open in new window

Anthony Perkins
Top Expert 2012

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

Author

Commented:
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.
ubsmail

Author

Commented:
Brand_Cat
ubsmail

Author

Commented:
Wow what a difference a index makes

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial