名称在sql server中计数 [英] names count in sql server

问题描述

i有这样的数据

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

所以可以一个人帮我这样做



谢谢&此致，



Prakash.ch

so could any one help me out for do like this

Thanks & Regards,

Prakash.ch

推荐答案

你想要类似的东西：

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

您好，



您也可以尝试这样做.. ..

Hi,

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'
T.deptcount
FROM (SELECT deptno, COUNT(deptno) 'deptcount'
FROM urTable
GROUP BY deptno) T

问候，

GVPrabu

Regards,
GVPrabu

选择myColumn，count（* ）totalcount

from myTable

group by myColumn

有点数（*）> 1

按次数排序（*）desc;





Kishor Makwana

软件工程师

Insight Softech

www.insightsoftech.com

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

这篇关于名称在sql server中计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！