Read date and show comma separated values in column
Table :
CREATE TABLE test
(
cola int,
colb date
);
Insert
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
which is entered on a specific date. And I want to calculate that the different dates that are present in the column colb
for a particular one cola
. And want to show dates with a comma with specific values cola
.
Expected Output :
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 the result . The above result shows what is cola
entered in 3 dates, but the distinctive is 2. Like the other values.
source to share
Use the Xml Path()
trick Distinct Count
of colb
, to do it.
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
source to share
Try this (non-XML version - clear database 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
source to share