GROUP_CONCAT is built-in function in MySQL To Achieve Following.
This Blog Explains How We Can Achieve This In Microsoft SQL Server
In MySQL Following Query is Required.
SELECT Designation,GROUP_CONCAT(EmpName)AS EmployeeList FROM Employee A GROUP BY Designation
Let’s See How we Can Achieve Above In Microsoft SQL Server
Select Designation, Substring((Select ',' + EmpName From Employee B Where B.Designation=A.Designation For XML Path('')),2,8000) As EmployeeList From Employee A Group By Designation
I have Used Substring Function To Remove Extra Comma From The Resultset
Great thank you :-)
ReplyDeletenice work dude....
ReplyDeleteI am not able to get this SQL to work especially getting errors in XML path syntax and internal reference to the same table
ReplyDeleteAt least one of the tables needs an alias. In the example above both tables have an alias {A and B}.
DeleteHi there,
ReplyDeleteHow can i use this method to select the column names from another table??
so i can build an sql query dynamically based on the results from another table.
This works out great. Thank You :)
ReplyDeleteThanks, this really helped me. Finally someone explains this with a simple query!
ReplyDeleteThanks, this helped me with my db project. Very Simple and does what I want.. :D
ReplyDeleteThanks this helped, is there any way we can only get the concatenated list of distinct values in the query output ?
ReplyDeleteNice work. Thanks!
ReplyDeletethanks.. still its a long process compared to mysql where you can use group_concat...
ReplyDeleteanyways, thank you dud
Select Designation,
ReplyDeleteSubstring((Select DISTINCT ',' + EmpName From Employee B Where B.Designation=A.Designation For XML Path('')),2,8000) As EmployeeList
From Employee A
Group By Designation
the above gives you the distinct values
perfect, thanks
ReplyDeletethis one trims of the value. incomplete result.
ReplyDeleteNice!
ReplyDeleteHELP ME PLEASE.
ReplyDeleteSelect ACT_ORIG_BALANCE from account where act_created_by ='2253999570687750'
OUTPUT
ACT_ORIG_BALANCE
26.63
22.72
27.99
26.15
28.13
EXPECTED OUTPUT- 26.63$#22.71$#27.99$#26.15$#28.13