SQL Interview Questions and answers Part 4 | How to get comma separated values in SQL

SQL Interview Questions and answers Part 4 | How to get comma separated values in SQL

Problem Statement:-
Emp_Details Table has four columns EmpID, Gender, EmailID and DeptID. User has to write a SQL query to derive another column called Email_List to display all Emailid concatenated with semicolon associated with a each DEPT_ID as shown below in output Table.

-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
*Instagram :
*Twitter:

-------------------------------------------------------------------------
Table and Insert SQL Script :
-------------------------------------------------------------------------
CREATE TABLE Emp_Details (
EMPID int,
Gender varchar,
EmailID varchar(30),
DeptID int)

----------------------------------------------------------------------------------------------------------------------
#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sql InterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #ITJunction4All #ETLQuestion

[adityanjsg]

This channels covers most relevant details in shortest time!

[balakrishnanramachandran]

great tutorial . please do more videos in data engineering domain . i wish you all the best

[roshanp]

Very nice videos, Sunil. It'd be really helpful if you also share the create/insert statements like you have done for a few problem. It will help us attempt the solution first before looking at your solution :)

[sssaamm]

Hey boss, this is awesome.keep uploading.hope you have a bright future.

[fungiri]

Assume some of the dept ID having a duplicate email IDs then how to take the distinct email Id using STT_AGG function

[TheEverydayAnalyst]

Thanks a ton! I was asked the exact same question during my interview.

[sumitmishra-ggdi]

For MySQL users
SELECT DEPTID, GROUP_CONCAT(EMAILID) FROM EMP_DETAILS
GROUP BY DEPTID

[pavanim]

Thank you So much @ItJunction4All Sunil for ur clear explanation and sharing such awesome videos.Im loooking to prepare for these kind of questions and luckily found ur channel.
Have a question.How can we Print the list of returning customers (Definition of returning customer: Customers who take more than 1 ride within any 7 day period).Could u pls let me know.
Sample Data:
id date cartype
1 15-Sep xl
2 16-Sep go
3 17-Sep suv
4 17-Sep xl
5 17-Sep xl
1 22-Sep xl
2 18-Sep go
3 25-Sep suv

[rahulv]

i used AWS redshift for similar solution::

select DeptID, lisagg(EmailIdD, ';') within group (order by EmailIdD) from Emp_Details order by DeptID

[sanjivanilahane]

Is this for database tester also?? Bcoz it looks little bit high level of query.

[arinjayjain]

How we got output order by DepID by default ?? why we do not need to write "order by depID" in the end? can someone explain to me.

[shivaniverma]

Hello! I'm getting this error-Msg 195, 'string_agg' is not a recognized built-in function name.
Can you please suggest what should I do?

[rajulapatiteja]

Sir Didn't u provide table in description to every question

[mritunzaysingh]

which function we can use in PostgreSQL for this query ...please answer sir or anyone it will helpful for me

[sarvesht]

Is it possible to do this without using any function ?

[yashmoyal]

What is within clause in this query do

[TheEsisia]

The XML PATH solutions do not order the emails alphabetically.

[imranpathan]

SElect DeptID, EmailList=
STUFF((Select ', '+EmailID from Emp_Details b Where a.DeptID=b.DeptID
For Xml Path('')), 1, 2, '')
from Emp_Details a
Group by DeptID

[GaneshNaduvathati]

select distinct deptid, (
select stuff((
select ', '+emailid
from Emp_Details a where a.DeptID=b.DeptID for xml path('')), 1, 1, ''))
from Emp_Details as b

[prajjwaljaiswal]

Can anyone solve without using string_AGG?