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.
phone · Jul 3, 2019 5:11 PM · 15,927 views
Use "GROUP BY" on DOB and get list of names in one column by using STRING AGG functions.
lazyketa · Jul 3, 2019 6:36 PM
select DATE_OF_BIRTH, LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY NAME) AS NAME from TABLE GROUP BY DATE_OF_BIRTH; Good Luck !!!
basnyatt · Jul 3, 2019 6:38 PM
Hello basnyatt, When I run the query it throws the error saying: "The function 'ListAgg' may not have a WITHIN GROUP clause."
phone · Jul 4, 2019 10:17 AM
what RDBMS are you using
basnyatt · Jul 5, 2019 10:21 AM
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.
raajkm · Jul 5, 2019 12:18 PM
Hello basnyatt, I am using Microsoft SQL Server 2012.
phone · Jul 6, 2019 1:21 PM
Hello raajkm, I need the skript for Microsoft SQL Server. Please help me. Last edited: 08-Jul-19 08:07 AM
phone · Jul 7, 2019 11:37 AM
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') Last edited: 08-Jul-19 12:16 PM Last edited: 08-Jul-19 12:17 PM
raajkm · Jul 8, 2019 12:14 PM
This conversation is preserved exactly as it was on the original Sajha.com and can't accept new replies.
Start a New Discussion