concatinating

[CrystalVis]

i have a table with the following data structure:

category instance nbr
out of bal 1 8131234567
out of bal 1 7272345678...

what i'm trying to accomplish is get the result in this format:

category instance nbr
out of bl 2 8131234567,7272345678

basically these two row is in the same category i want to list the category the count of the category and the number associates to the category in one row. your help is greatly appreciated. thanks

 

[zhavic]

create function like this:

CREATE FUNCTION fn_get_list_of_numbers( @category  varchar(20) )
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @list varchar(4000)

	SET @list = ''
	SELECT @list = @list + nbr + ','
		FROM your_table
		WHERE category = @category

	IF @@ROWCOUNT > 0
		/* cut out last coma */
		SET @list = LEFT( @list, LEN( @list ) - 1 )

	RETURN @list
END

and than run this select statement:

SELECT category, SUM( instance ) AS instance, dbo.fn_get_list_of_numbers( category ) AS nbr
	FROM your_table
	GROUP BY category

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[ESquared]

Here's a method I developed to do this in one query. You might also like to consult the FAQ in this form about concatenating a one-to-many relationship.

SET NOCOUNT ON

CREATE TABLE #t (
   Deal int,
   Competitor varchar(10))

INSERT INTO #t
   SELECT 1,'X' UNION
   SELECT 1,'Y' UNION
   SELECT 1,'Z' UNION
   SELECT 2,'X' UNION
   SELECT 3,'Y' UNION
   SELECT 4,'Y' UNION
   SELECT 4,'X' UNION
   SELECT 5,'W' UNION
   SELECT 5,NULL UNION
   SELECT 5,'V'


SELECT
      Seq.Deal,
      List = SUBSTRING(
         Max(IsNull(CASE Seq.Cnt WHEN 1 THEN Cmp END,''))
         + Max(IsNull(CASE Seq.Cnt WHEN 2 THEN Cmp END,''))
         + Max(IsNull(CASE Seq.Cnt WHEN 3 THEN Cmp END,''))
         + Max(IsNull(CASE Seq.Cnt WHEN 4 THEN Cmp END,''))
         + Max(IsNull(CASE Seq.Cnt WHEN 5 THEN Cmp END,''))
         + Max(IsNull(CASE Seq.Cnt WHEN 6 THEN Cmp END,''))
         + Max(IsNull(CASE Seq.Cnt WHEN 7 THEN Cmp END,''))
         + Max(IsNull(CASE Seq.Cnt WHEN 8 THEN Cmp END,''))
         + Max(IsNull(CASE Seq.Cnt WHEN 9 THEN Cmp END,''))
         + Max(IsNull(CASE Seq.Cnt WHEN 10 THEN Cmp END,''))
      ,2,7999)
   FROM
      (SELECT
         T1.Deal, Cmp = ',' + T1.Competitor, Cnt = Count(T2.Deal)
         FROM #t T1 INNER JOIN #t T2 ON T1.Deal = T2.Deal AND T1.Competitor >= T2.Competitor
         GROUP BY T1.Deal, T1.Competitor
      ) Seq
   GROUP BY Seq.Deal

DROP TABLE #t

Note that this version won't suit if you have many items to list... you can extend it to allow more than the 10 I did, but there's an upper limit to practicality.