Database Simplified Headline Animator

Database Simplified

Monday 13 June 2011

GROUP_CONCAT in Microsoft SQL Server

 

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

imageimageimage

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

image

Smile

16 comments:

  1. Great thank you :-)

    ReplyDelete
  2. I am not able to get this SQL to work especially getting errors in XML path syntax and internal reference to the same table

    ReplyDelete
    Replies
    1. At least one of the tables needs an alias. In the example above both tables have an alias {A and B}.

      Delete
  3. Hi there,

    How 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.

    ReplyDelete
  4. This works out great. Thank You :)

    ReplyDelete
  5. Thanks, this really helped me. Finally someone explains this with a simple query!

    ReplyDelete
  6. Thanks, this helped me with my db project. Very Simple and does what I want.. :D

    ReplyDelete
  7. Thanks this helped, is there any way we can only get the concatenated list of distinct values in the query output ?

    ReplyDelete
  8. Nice work. Thanks!

    ReplyDelete
  9. thanks.. still its a long process compared to mysql where you can use group_concat...

    anyways, thank you dud

    ReplyDelete
  10. Select Designation,
    Substring((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

    ReplyDelete
  11. perfect, thanks

    ReplyDelete
  12. this one trims of the value. incomplete result.

    ReplyDelete
  13. HELP ME PLEASE.

    Select 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

    ReplyDelete