sql comma delimited list of a column in analytical function

Question

I am using SQL server 2008, and I need to make a common delimeted list of a column. I know how to do it, but I need this time while I use analytical function, I mean I don't want to use group by clause. Since I will also select the records in outer query "where row_num=1"

Here is the query:

SELECT UserId
      ,ProductList
      ,Value
  FROM
  (
SELECT p.UserId 
     ,p.Value
     , ROW_NUMBER()OVER (PARTITION BY p.UserId ORDER BY p.RecordCreateDate asc) AS 'row_num'
     --here I need a solution  OVER (PARTITION BY p.UserId) AS 'ProductList'
  FROM Products p
       INNER JOIN
       Users u
       ON p.UserId = u.Id
       ) result
 WHERE result.row_num = 1

Users data:

Id       Name      ....
 1       John
 2       Anton
 3       Craig

Products data:

Id      UserId      Name     RecordCreateDate   Value
 1           1         a           21.12.2012      10
 2           1         b           11.12.2012      20
 3           1         c           01.12.2012      30
 4           2         e           05.12.2012      40
 5           2         f           17.12.2012      50
 6           3         d           21.12.2012      60 
 7           3         i           31.12.2012      70

I need a result such as:

UserId     ProductList      Value
     1           a,b,c         30
     2             e,f         40
     3             d,i         60

Thanks for your help

possible duplicate of [Concatenate values based on ID](http://stackoverflow.com/questions/6603319/concatenate-values-based-on-id) — Aaron Bertrand, Feb 07 '12 at 17:46
you didn't get the point, I don't want to use group by clause — Mehmet, Feb 07 '12 at 17:52
The result you're asking for doesn't make sense to me. How does the row_number() ever come into effect? How do you expect to have any case where row_number <> 1 for any userID? Can you please show sample data and desired results (including results that would allow your "where row_num = 1" requirement make sense? I didn't "get the point" because you didn't explain well enough. I don't see row_num in your "I need a result such as:" section, so I have no idea what row_num you're expecting in each row. Also are you sure that both UserId and RecordCreatedDate come from the products table? — Aaron Bertrand, Feb 07 '12 at 17:52
here where I will use row_num: SELECT UserId ,ProductList FROM ( SELECT p.UserId , ROW_NUMBER()OVER (PARTITION BY p.UserId ORDER BY p.RecordCreateDate asc) AS 'row_num' --here I need a solution OVER (PARTITION BY p.UserId) AS 'ProductList' FROM Products p INNER JOIN Users u ON p.UserId = u.Id ) result WHERE result.row_num = 1 — Mehmet, Feb 07 '12 at 17:55
row_num is just used like group by. I don't want to use group by, since I will need extra join with the Products table again to get the record — Mehmet, Feb 07 '12 at 17:58
Please show sample data and desired results. Trying to read your pseudo-code in a comment is impossible. And don't worry about telling us whether you want to use or not use constructs like group by - maybe we know an efficient way to do that that you're not thinking of. Think about your question as "these are the results I want" not "this is how I want to get the results." — Aaron Bertrand, Feb 07 '12 at 17:59
Thanks Aaron you are right, now I edited and added sample data — Mehmet, Feb 07 '12 at 18:12
Mikael's solution is what you need, use use the STUFF function to pull together your products — msmucker0527, Feb 07 '12 at 18:17
Your value 70 doesn't make sense - that's the value associated with the newest RecordCreateDate (unless Dec 31 now comes before Dec 21). — Aaron Bertrand, Feb 07 '12 at 18:19
If you need information from the users table (e.g. name), then PLEASE post a complete question, e.g. add the desired information to your "I need a result such as:" section. Incomplete questions lead to incomplete answers. — Aaron Bertrand, Feb 07 '12 at 18:41

score 3 · Answer 1 · answered Feb 07 '12 at 18:00

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

Thanks. I will try it, and let you know – Mehmet Feb 07 '12 at 18:24 — Mehmet, Feb 07 '12 at 18:24

msmucker0527 · Answer 2 · 2012-02-07T18:11:16.963

1

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

edited Feb 07 '12 at 18:11

answered Feb 07 '12 at 17:56

msmucker0527

5,164
2
22
36

1

He seems to need the row_num in there as well, which I suppose represents the earliest product created by each user. But partitioned by UserID I don't understand how he could ever get anything other than 1 for each userID. – Aaron Bertrand Feb 07 '12 at 17:58

Aaron Bertrand · Accepted Answer · 2022-01-23T17:36:12.027

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

Thanks. I can't see Users table, and do I need to use(join) it after CTE query? — Mehmet, Feb 07 '12 at 18:36
What do you need from the Users table? There is nothing in your desired results that indicates anything is needed from that join. Is there still some information missing from your question? — Aaron Bertrand, Feb 07 '12 at 18:37

sql comma delimited list of a column in analytical function

3 Answers3