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:
Hi,

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,

Prakash.ch
Posted
Updated 30-May-13 22:12pm
v2

You want something similar to this:
SQL
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
 
Comments
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    
5+
Hi,

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

Regards,
GVPrabu
 
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
www.insightsoftech.com
 
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