15,945,119 members
See more:
Hi,

i have the data like this
```deptno   empname

1        raju
1        mohan
1        mahesh
2        alekya
3        srinivas
3       bharat```

now my desired output is

```deptno         empname                                  count
2         alekya                                          1

so could any one help me out for do like this

Thanks & Regards,

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

## Solution 3

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

## Solution 2

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

## Solution 1

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```

prakash.chakrala 31-May-13 4:32am
and i need to get count also.
OriginalGriff 31-May-13 4:37am