​Hello , I have a table called Student consisting of Columns Name, and DateOfBirth. I would like to create a query Which Selects all Name whose DateOfBirth is on the same day. The DateOfBirth Column datatype is in DateTime format. I want the result in the Date format. In my table below I want row 1,2, and 7 for day 1 and 3,4 for day 2 as a result of the query.

Use "GROUP BY" on DOB and get list of names in one column by using STRING AGG functions.

select DATE_OF_BIRTH, LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY NAME) AS NAME
from TABLE
GROUP BY DATE_OF_BIRTH;

Good Luck !!!

Hello basnyatt,

When I run the query it throws the error saying:
"The function 'ListAgg' may not have a WITHIN GROUP clause."

what RDBMS are you using

select x.*, rownum as day_num from (
select to_date((to_char(dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD') date_of_Birth, listagg(Name, ',') within group(order by Name) Name_of_students
from student
group by to_date((to_char(dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD')
) x;


this works for Oracle. If you are using mysql try using string_agg instead of listagg.

Hello basnyatt,

I am using Microsoft SQL Server 2012.

Hello raajkm,

I need the skript for Microsoft SQL Server. Please help me.

WELL i do not use sql server 2012 but you might use the query as;

select to_date((to_char(S1.dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD') date_of_Birth,
stuff ((select distinct ','+ Name from student s2 where s2.name=s1.name FOR XML PATH(' ')),1,1,' ') as name_of_students

from student s1 group by to_date((to_char(S1.dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD')