SQL逗号分隔列表中的分析函数列表

Question

我正在使用SQL Server 2008，并且需要制作一个列的公共列表。我知道如何去做，但我需要这段时间，而我使用分析功能，我的意思是我不想使用group by子句。 由于我也将选择在外部查询中的记录 “其中ROW_NUM = 1”

下面是查询：

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 

用户数据：

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

产品数据：

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 

我需要一个结果如：

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

感谢您的帮助

Answer 1

只是为了保持完整性。删除实际解决方案的＃符号。

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); 

查询：

;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('.', 'varchar(max)'),1,1,''), 
    x.Value 
FROM x 
INNER JOIN #users AS u 
ON x.UserId = u.Id 
WHERE x.row_num = 1; 

然后清理：

DROP TABLE #users, #products; 

结果：

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

[基于ID串联值]的

Answer 2

我不知道你问的是什么开始，但是这会给你所要求的输出

SELECT UserId, 
    STUFF((SELECT ',' + ProductName from Products p WHERE p.UserID = u.UserID FOR XML PATH('')), 1, 1, '') as ProductList 
FROM Users u 

Answer 3

既然你要过滤在row_num = 1上，您需要将您的查询放入CTE中或包含Products中额外列的类似内容中。然后，您可以使用for XML路径技巧在外部查询中构建逗号分隔的字符串，而无需使用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