Reduce returned rows by concatenating strings 1

[Glasgow]

When I execute the following query:

SELECT CustId, Product FROM Orders

I get results:

123, Saw
123, Screwdriver
123, Screwdriver
123, Hammer
112, Saw
112, Pliers
112, Pliers
189, Plane

What I would like to do is write a query that returns the results in fewer rows by concatenating the product descriptions for each customer - e.g.

123, Saw/Screwdriver/Screwdriver/Hammer
112, Saw/Pliers/Pliers
189, Plane

Any suggestions?

Thanks in advance

 

[Glasgow]

I have got a solution of sorts. By writing a function as follows:

CREATE FUNCTION FnuPrdList(@ConId int) RETURNS varchar(500)
AS
BEGIN
  DECLARE @ReturnVar    varchar(500)
  SET     @ReturnVar=''
  SELECT  @ReturnVar=@ReturnVar + Product + ','
    FROM  Orders
   WHERE  CustId = @ConId
  RETURN  @ReturnVar
END

Then referncing it in a query as follows:

select CustId, Vals= dbo.FnuPrdList(CustId)
 from Orders GROUP BY CustId

Though I accept this is perhaps not the most efficient route and will not allow for an unlimited number of products.

 

[ca8msm]

faq183-6466 and faq183-6485 should give you a good starting point. You might also want to check out the COALESCE function.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[gmmastros]

There is nothing inherently wrong with this approach. In fact, it's how I would have recommended it. There are a couple suggestions that I would like to add (just to improve this a little).

[COLOR=blue]CREATE[/color] [COLOR=#FF00FF]FUNCTION[/color] FnuPrdList(@ConId [COLOR=blue]int[/color]) RETURNS [COLOR=blue]varchar[/color]([!]8000[/!])
[COLOR=blue]AS[/color]
[COLOR=blue]BEGIN[/color]
  [COLOR=blue]DECLARE[/color] @ReturnVar    [COLOR=blue]varchar[/color]([!]8000[/!])
  [COLOR=blue]SET[/color]     @ReturnVar=[COLOR=red]''[/color]
  [COLOR=blue]SELECT[/color]  @ReturnVar=@ReturnVar + [COLOR=blue]Product[/color] + [COLOR=red]','[/color]
    [COLOR=blue]FROM[/color]  Orders
   [COLOR=blue]WHERE[/color]  CustId = @ConId
          [!]And Product Is Not NULL[/!]
  [COLOR=blue]Order[/color] [COLOR=blue]By[/color] [COLOR=blue]Product[/color]

  [COLOR=green]-- Remove the last comma
[/color]  [COLOR=blue]If[/color] [COLOR=#FF00FF]Right[/color](@ReturnVar, 1) = [COLOR=red]','[/color]
    [COLOR=blue]Set[/color] @ReturnVar = [COLOR=#FF00FF]Left[/color](@ReturnVar, Len(@ReturnVar) - 1)

  [COLOR=blue]RETURN[/color]  @ReturnVar
[COLOR=blue]END[/color]

1. Set the length of the return var to 8000.
2. Only concatenate where Product is not null. Nulls in the middle of the list would cause problems.
3. Sort the products alphabetically.
4. Remove the last comma that was added to the string.

Of course, #3 is optional depending on how you want your data to look.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Glasgow]

These are all valid suggestions George] thanks. Indeed I have already implemented the varchar(8000) one and I knew I'd have to get rid of that final comma! This is actually just a simplified illustration of the 'true' problem in which I have quite a bit more to do.

 

[christer99]

How can I modify the above example so it returns two values in a table, the 2nd value being row identifier. I need to tie the output of the function to another table and I don't see how I can do it without the row identifier (the ID).

 

[Glasgow]

Not sure quite what you're aiming for. Are you trying to get a string that concatenates the ID's? If so, why not just set up two functions - the second to concatenate the ID's?

Or perhaps illustrate your problem by extending my noddy saw/screwdriver/pliers data.

 

[Lamprey13]

Here is another way, not that you need it:

DECLARE @Orders TABLE(CustID INT, Product VARCHAR(50))

INSERT @Orders
SELECT 123, 'Saw'
UNION ALL SELECT 123, 'Screwdriver'
UNION ALL SELECT 123, 'Screwdriver'
UNION ALL SELECT 123, 'Hammer'
UNION ALL SELECT 112, 'Saw'
UNION ALL SELECT 112, 'Pliers'
UNION ALL SELECT 112, 'Pliers'
UNION ALL SELECT 189, 'Plane'


SELECT DISTINCT	
	CustID,
	STUFF((
			SELECT TOP 100 PERCENT '/' + t.Product 
			FROM @Orders AS t 
			WHERE t.CustID = o.CustID 
			-- ORDER BY '/' + t.Product -- Optional if you want order to the concatination
			FOR XML PATH('')), 1, 1, '') AS Product
FROM		
	@Orders AS o

-Ryan