比如:我用select department,userName from users从表中查询出如下数据
department | userName
--------------- --------------
it it1
it it2
it it3
ur ur1
ur ur2
我能不能用什么SQL对department进行分组然后变成如下的结果呢?
department | userName
--------------- --------------
it it1_it2_it3
ur ur1_ur2
--
以下查询仅知用于 sql 2005 及之后的版本
SELECT
*
FROM (
SELECT DISTINCT
department
FROM 你的表
)A
CROSS APPLY(
SELECT userNames = (
SELECT
[ * ] = userName
FROM 你的表
WHERE department = A.department
FOR XML PATH( '' ), TYPE
).value( ' / ' , ' nvarchar(max) ' )
)B
SELECT
*
FROM (
SELECT DISTINCT
department
FROM 你的表
)A
CROSS APPLY(
SELECT userNames = (
SELECT
[ * ] = userName
FROM 你的表
WHERE department = A.department
FOR XML PATH( '' ), TYPE
).value( ' / ' , ' nvarchar(max) ' )
)B
原地址: http://topic.csdn.net/u/20080730/10/93459f31-4327-49d4-ae16-9f65566c90ac.html