Count date and show into comma separated values in a column

Question

Table:

CREATE TABLE test
(
cola int,
colb date
);

Insertion:

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

Note: I want to show cola who is entered in the specific date. And want to count that distinct dates which is present in the colb column occured for specific cola. And want to show comma separted dates along to specific cola values.

Expected Result:

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 

Explanation of result: The above result shows that cola entered in 3 dates but the distinct are 2.Like that the other values are appeared.

Answer 1

Use Xml Path() trick with Distinct Count of colb to do this.

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 

Result

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

Try this (version without using XML - clear set-base approach with recursive CTE)

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

Result

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