sql server实现mysql的group_concat()功能

格式:

SELECT userid, LEFT(联合后的新字段名, LEN(联合后的新字段名) - 1) AS 联合后的新字段名
FROM dbo.表名 AS extern CROSS APPLY
   (SELECT 要concat的字段名 + ',' FROM dbo.表名 AS intern
    WHERE extern.userid = intern.userid FOR XML PATH('')) pre_trimmed(联合后的新字段名)
GROUP BY userid, 联合后的新字段名;

例子:读取info中每个用户的用户id和拥有的域名

表结构:

userid   domain

1        www.hfpph.com

1        www.qudou-2011.com

2        www.meibai11.com

代码:

SELECT userid, LEFT(domains, LEN(domains) - 1) AS domains
FROM dbo.info AS extern CROSS APPLY
   (SELECT domain + ',' FROM dbo.info AS intern
    WHERE extern.userid = intern.userid FOR XML PATH('')) pre_trimmed(domains)
GROUP BY userid, domains;

by 雪洁 2012-01-14 20:22:29 4419 views
我来说几句

相关文章