show columns with same ID in the same row

yuvalk · January 31st, 2007, 08:17 AM

Hi,

I have a table with 2 columns :
ID Product
----- ----------
1 a
2 b
3 c
1 h

how can I create a query that will return :

ID Product
----- ----------
1 a , h
2 b
3 c

meaning - will "group" the "product" column values by the ID column.

Thank you,

Yuval

Yuval Kronenfeld
Israel

dperiut · January 31st, 2007, 12:49 PM

Yuval: How many ID's are you dealing with?

Daniel
Scottsdale, AZ

yuvalk · February 1st, 2007, 11:18 AM

hI Daniel,

up to 1000 or so...

just to note - the ID Field is not a unique field - I just wanted to make it a short example.

Thanks,
yuval

Yuval Kronenfeld
Israel

dperiut · February 2nd, 2007, 12:19 PM

Hi Yuval,

That's a lot. If it was a small set I was going to recommend you nest a sub query with aggregated case statements. The only solution I can come up with is that you create a scalar CLR function, pass ID to it, loop through all the values of PRODUCT and return STRING. That or a extended stored PROC that you can nest into a query that groups by ID.

Regards,

Jeff Moden · February 2nd, 2007, 07:46 PM

Yuvalk,

Do you know how to create the function everyone is talking about?

It would look something like this

Code:

CREATE FUNCTION dbo.fnConcatProduct
        (
        @pID INT
        )
RETURNS VARCHAR(8000)
     AS
  BEGIN
        --===== Declare a return variable to hold concatenated CSV info
        DECLARE @rCSVData VARCHAR(8000)

        --===== Concatenate the data based on the inputs
         SELECT @rCSVData = ISNULL(@rCSVData+',','')+Product
           FROM YourTable
          WHERE ID = @pID

        --===== Return the concatenated CSV info
         RETURN @rCSVData
    END

and the query would look something like this...

Code:

SELECT DISTINCT

Code:

        ID,dbo.fnConcatProduct(ID) AS ProductCSV
   FROM YourTable

...of course, you would need to change the table name and perhaps the column names to match your table.
--Jeff Moden