group by problem?

[manj]

I have a table on sql server with the following data:
2 columns

1 mug
1 glass
2 book
2 pad...

Is it possible to construct a query to create the following:

1 mug, glass
2 book, pad
3 ...
etc

 

[tlbroadbent]

There is no "simple" function that will produce the result you want. Here is a script that you can use. It handles up to 4 entries per ID. You can add more left joins to increase the number of entries that wil be handled.

I suggest that you create a stored procedure from the script. Change the column names and types to match your table.

-- Create a temp table with an identity column
CREATE TABLE #tmp
(ID int, Cat varchar(10),
RecNo int identity (1,1))

-- Insert records into the temp table
INSERT Into #tmp
SELECT Id, Cat
FROM table1
ORDER BY ID, Cat

-- Select records and concatenate
SELECT T.ID,
T.cat +
CASE
WHEN IsNull(T1.cat,'')='' THEN ''
ELSE ', ' + T1.cat
END +
CASE
WHEN IsNull(T2.cat,'')='' THEN ''
ELSE ', ' + T2.cat
END +
CASE
WHEN IsNull(T3.cat,'')='' THEN ''
ELSE ', ' + T3.cat
END as CharResult
FROM #tmp T
LEFT JOIN #tmp T1
ON T.ID=T1.ID
AND T1.RecNo=T.RecNo+1
LEFT JOIN #tmp T2
ON T.ID=T2.ID
AND T2.RecNo=T1.RecNo+1
LEFT JOIN #tmp T3
ON T.ID=T3.ID
AND T3.RecNo=T2.RecNo+1
WHERE T.RecNo =
(SELECT min(TT.RecNo)
FROM #tmp TT
WHERE TT.ID=T.ID)

DROP TABLE #tmp

-----------------------------

NOTE: I suggest that you post SQL Server questions in the SQL Server forum (Forum183) rather than the ANSI SQL forum. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.