Click here to Skip to main content
15,884,388 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi Friends,

I have a doubt in sql server 2005. I have a some records like

No Name
1 aaa
1 bbb
2 ccc
1 ddd
3 eee
2 fff

I want the result like :

No Name
1 aaa,bbb,ddd
2 ccc,fff
3 eee

I tried : select no,name from table1 group by no,name.

It will give result like my above table format... but i want the below format.How can write query?

No Name
1 aaa,bbb,ddd
2 ccc,fff
3 eee
Posted
Comments
berrymaria 12-Jul-13 3:42am    
you accepted my answer.. and then you undo it.. then you accepted it .. then undo.. then accepted.. then undo..

Try this:

SQL
SELECT No,
       SUBSTRING(
       (Select (',' + Name)
       from Temp_Table t2
       where t1.No=t2.No
       order by
       No,
       Name
       FOR xml path( '' ) ),3,1000)
       FROM Temp_Table t1
       group by No
 
Share this answer
 
Comments
jaideepsinh 12-Jul-13 3:46am    
5+ve.
Priyanka7777 12-Jul-13 3:47am    
Thnx.
Hi, there is no equivalent function such a GROUP_CONCAT in MySQL but I found an interesting trick, please try:


SQL
SELECT
[No],
STUFF((
SELECT ', ' + CAST([Name] AS VARCHAR(MAX)) FROM [table1] WHERE [No] = [table1_copy].[No]
FOR XML PATH ('')),
1,2,'') AS [Name_Values]
FROM [table1] [table1_copy]
GROUP BY [No]
 
Share this answer
 
It works for me, try this:

SQL
Select distinct ST2.NO, 
           (Select ST1.NAME + ',' AS [text()]
            From dbo.AA ST1
            Where ST1.NO = ST2.NO
            ORDER BY ST1.NO
            For XML PATH ('')) [Name]
     From dbo.AA ST2
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900