Click here to Skip to main content
15,936,602 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

i have the data like this
deptno   empname

1        raju
1        mohan
1        aditya
1        mahesh
2        alekya
3        maduri
3        srinivas
3       bharat

now my desired output is

deptno         empname                                  count
1         raju, mohan, aditya,mahesh                      4
2         alekya                                          1
3         maduri,srinivas,bharat                          3

so could any one help me out for do like this

Thanks & Regards,
Updated 30-May-13 22:12pm

You want something similar to this:
SELECT deptno, empname=
   STUFF((SELECT ',' + CONVERT(VarChar(10), empname)
      FROM myTable b
      WHERE b.deptno = a.deptno
      FOR XML PATH('')), 1, 1, '')
FROM myTable a
GROUP BY deptno
Share this answer
prakash.chakrala 31-May-13 4:32am    
and i need to get count also.
OriginalGriff 31-May-13 4:37am    
So add it!
damodara naidu betha 28-Jun-13 1:55am    

You can try like this also....
SELECT T.deptno,
STUFF((SELECT ','+empname [text()] FROM urTable WHERE deptno=T.deptno FOR XML PATH('')),1,1,'') 'empname'
FROM (SELECT deptno, COUNT(deptno) 'deptcount'
FROM urTable
GROUP BY deptno) T

Share this answer
Select myColumn, count(*) totalcount
from myTable
group by myColumn
having count(*) >1
order by count(*) desc;

Kishor Makwana
Software Engineer
Insight Softech
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