Comma Separated values in SQL | XML PATH | SQL to XML Format | Row Tag| Root Tag | SQL Interview Q&A

SQL Scripts used in this video, you can run and explore:

CREATE TABLE Employees (empname varchar(50) , empLocation varchar(50))

INSERT Employees Values ('smith','Newyork'),('Jones','London'),('smith','Scotland')
,('Adams','Chicago'),('Jones','Dallas')

select * From Employees

select * From Employees FOR XML PATH;

select * From Employees FOR XML PATH ('Employee');

select * From Employees FOR XML PATH ('');

select * From Employees

select DISTINCT EmpName,
STUFF(( SELECT ',' + Emplocation FROM Employees b WHERE a.EmpName = b.EmpName FOR XML PATH('')),1,1,'') EMpLocation
From Employees a

select * From Employees FOR XML PATH ('Employee'), ROOT ('Employees');

Facebook Page:

YouTube Channel:

This channel would provide new videos on SQL, ETL and Data warehouse concepts. I would create a separate play list for SQL Tutorials for beginners, advanced concepts, Interview questions and data warehousing concepts

[yairteshuva]

Great video!
I get this error:
The data types varchar and text are incompatible in the add operator
Have not been able to fix it.
Do you know how?
Thanks

[Choudhary_Sh]

Can we use String_Agg (String Aggregate) for this ?

[malliarjun]

If we have multiple locations for one employee
How to get comma separated for only first and last locations
Like
Smith hyd
Smith bang
Smith pune
Smith del

John null
John mum
John ncr
John null


But out put should :

Smith, hyd, del
John, mum, ncr