sql server programming: Hi all,

I have an urgent requirement. I have a table which looks like

ID1 ID2 Value
1 1 a
1 2 b
2 1 c
2 2 d
2 3 e

where ID1 and ID2 is the composite primary key.

I want this to appear as

ID1 ID2 Value
1 1 a,b
2 1 c,d,e

Is it possible with a single query? Can I create a view somehow? I
could write a function or procedure for this but this is a very small
part of a very large query, so I was looking to do it without any
looping. Can somebody please help? I am using SQL Server 2000
Enterprise Edition.

Thanks.

You can do this very easily with the RAC utility:
http://www.rac4sql.net/onlinehelp.asp?topic=236

The above is functionally equivalent to the MySql concat function.

Note RAC has other options to solve these types of problems.

RAC v2.2 and QALite @
www.rac4sql.net

Here is an example.

--DELETE FROM cases

CREATE TABLE Cases(caseid VARCHAR(15), userid int)
INSERT INTO Cases
SELECT
'DEMO0021H', 3
UNION ALL SELECT
'DEMO0021H', 6
UNION ALL SELECT
'DEMO0021H', 7
UNION ALL SELECT
'DEMO0021H', 8
UNION ALL SELECT
'H1b-34234', 3
UNION ALL SELECT
'H1b-34234', 6
UNION ALL SELECT
'H1b-34234', 8
UNION ALL SELECT
'L-34234', 3
UNION ALL SELECT
'StandardExp2121', 3
GO

CREATE FUNCTION dbo.getUsers(@caseid VARCHAR(15))
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @vchUserList VARCHAR(400)
SET @vchUserList = ''
SELECT @vchUserList = @vchUserList +
CASE WHEN @vchUserList= '' THEN '' ELSE ', ' END + CONVERT(VARCHAR, UserId)
FROM Cases
WHERE caseid = @caseid

RETURN @vchUserList
END
GO

SELECT caseid, dbo.getUsers(caseid) as usrs
FROM cases
GROUP BY caseid



--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com


[quoted text, click to view]