sql comma delimited list of a column in analytical function
Since you are going to filter on row_num = 1
you need to put your query in a CTE or the likes where you include the extra columns from Products. Then you can build your comma separated string in the outer query using the for XML path trick without using group by.
;WITH C as
(
SELECT p.UserId
, ROW_NUMBER()OVER (PARTITION BY p.UserId ORDER BY p.RecordCreateDate asc) AS 'row_num'
--, Some other fields from Products
FROM Products p
INNER JOIN
Users u
ON p.UserId = u.Id
)
SELECT UserId,
--, Some other fields from Products
--, Build the concatenated list here using for xml path()
FROM C
WHERE C.row_num = 1
Just for completeness. Remove the # symbols for your actual solution.
SET NOCOUNT ON;
CREATE TABLE #users
(
Id INT,
Name VARCHAR(32)
);
INSERT #users VALUES
(1,'John'),
(2,'Anton'),
(3,'Craig');
CREATE TABLE #products
(
Id INT,
UserId INT,
Name VARCHAR(32),
RecordCreateDate DATE,
Value INT
);
INSERT #products VALUES
(1,1,'a','2012-12-21',10),
(2,1,'b','2012-12-11',20),
(3,1,'c','2012-12-01',30),
(4,2,'e','2012-12-05',40),
(5,2,'f','2012-12-17',50),
(6,3,'d','2012-12-21',60),
(7,3,'i','2012-12-31',70);
The query:
;WITH x AS
(
SELECT UserId, Value,
row_num = ROW_NUMBER() OVER
(
PARTITION BY UserId
ORDER BY RecordCreateDate
)
FROM #products
)
SELECT
x.UserId,
u.Name,
ProductList = STUFF((
SELECT ',' + Name
FROM #Products AS p
WHERE p.UserId = x.UserId
FOR XML PATH(''),
TYPE).value(N'./text()[1]', N'varchar(max)'),1,1,''),
x.Value
FROM x
INNER JOIN #users AS u
ON x.UserId = u.Id
WHERE x.row_num = 1;
Then clean up:
DROP TABLE #users, #products;
Results:
UserId Name ProductList Value
1 John a,b,c 30
2 Anton e,f 40
3 Craig d,i 60
I'm not sure what you're asking in the beginning, but this will give you the requested output
SELECT UserId,
STUFF((SELECT ',' + ProductName from Products p WHERE p.UserID = u.UserID FOR XML PATH('')), 1, 1, '') as ProductList
FROM Users u