能否有这样的汇总查询?
本帖最后由 kncomputer 于 2013-09-05 02:05:51 编辑 数据库:SQL2008
表:
id-----Uid----Data
1 ---- A ----- aa
2 ---- A ----- bb
3 ---- A ----- cc
4 ---- B ----- dd
5 ---- B ----- ee
如何汇总出:
Uid----CC----Datas
A ----- 3 --- aa;bb;cc;
B ----- 2 --- dd;ee;
====================
select Uid, Count(id) CC,(如何得到Datas字段)From TABLE group by uid
谢谢!
[解决办法]
CREATE TABLE #tbl(id INT IDENTITY(1,1),UID VARCHAR(10),DATA VARCHAR(10))
INSERT INTO #tbl VALUES('A','aa'),('A','bb'),('A','cc'),('B','ee'),('B','dd')
SELECT Uid,Count(id) CC, datas=STUFF((SELECT ','+data FROM #tbl WHERE uid=a.uid FOR XML PATH('')),1,1,'')
FROM #tbl a
group BY uid