在线求一SQL语句
有表Employee
Name Department
aaaa A班
bbbb A班
cccc B班
dddd C班
怎么样有一条语句列出在A班和B班的信息，并且再把A班的信息放在一个控件里，B班的信息放在一个控个里。 SQL
[解决办法]
WITH temp (name, department)
AS (SELECT 'aaaa',
'a班'
UNION ALL
SELECT 'bbbb',
'a班'
UNION ALL
SELECT 'cccc',
'b班'
UNION ALL
SELECT 'dddd',
'c班')
SELECT department,
Stuff((SELECT ',' + CONVERT(VARCHAR(4), name)
FROM temp
WHERE department = A.department
ORDER BY department
FOR xml path('')), 1, 1, '') AS name
FROM temp A
GROUP BY department
/*
department name
---------- ---
a班 aaaa,bbbb
b班 cccc
c班 dddd
(3 行受影响)
*/
