在列中计数日期并显示为逗号分隔的值

Question

表

CREATE TABLE test
(
cola int,
colb date
);

插入

insert into test values(111,'2014-3-2');
insert into test values(111,'2014-3-3');
insert into test values(111,'2014-3-2');
insert into test values(121,'2014-4-1');
insert into test values(121,'2014-4-2');
insert into test values(121,'2014-4-3');
insert into test values(121,'2014-4-4');
insert into test values(131,'2014-5-1');
insert into test values(131,'2014-5-1');

备注：我想向cola显示谁是在特定日期输入的。并希望计算colb列中出现的针对特定cola的不同日期。并希望显示分隔到特定cola值的逗号分隔日期。

预期结果：

cola    CountOfDates                  colb
-----------------------------------------------------------------
111         2          2014-03-02,2014-03-03
121         4          2014-04-01,2014-04-02,2014-04-03,2014-04-04 
131         1          2014-05-01 

对结果的解释:上面的结果显示，cola输入了3个日期，但不同的是2。

Answer 1

使用Xml Path()技巧和Distinct Count of colb来完成这个任务。

SELECT cola,
       Count(distinct colb)                             Countofdates,
       Stuff((SELECT Distinct ',' + CONVERT(VARCHAR(15), colb )
              FROM   #test t
              WHERE  t.cola = a.cola
              FOR XML PATH ('')), 1, 1, '') colb
FROM   #test a
GROUP  BY cola 

结果

cola    Countofdates    colb
----    ------------    -------------------------------------------
111     2               2014-03-02,2014-03-03
121     4               2014-04-01,2014-04-02,2014-04-03,2014-04-04
131     1               2014-05-01

Answer 2

试试这个版本(不使用带有递归CTE的XML - clear base方法)

with [base] as
(
    select cola, cast(colb as nvarchar(max)) [colb], 1 [count] from test
    union all
    select b.cola, b.colb+ ',' + cast(t.colb as nvarchar(10)), [count]+1
    from [base] b
    join test t on t.cola = b.cola
        and b.colb not like '%' + cast(t.colb as nvarchar(10)) + '%'
)
, ranked as
(
    select cola
    , colb
    , [count]
    , row_number() over (partition by cola order by [count] desc) [rank] 
    from [base]
)
select cola, colb, [count] from ranked where [rank] = 1

结果

cola    colb                                            count
-------------------------------------------------------------
111     2014-03-02,2014-03-03                           2
121     2014-04-01,2014-04-02,2014-04-03,2014-04-04     4
131     2014-05-01                                      1