Declare @A Table(UID Int, UName Varchar(20))
Declare @B Table(SID Int, UID Int, SName Varchar(20))

Insert @A Select 1, 'AAA' Union All Select 2, 'BBB'
Insert @B Select 1, 1, 'EEE' Union All Select 1, 2, 'QQQ' Union All Select 2, 1, 'GGG' Union All Select 2, 2, 'HHH'

SELECT A.UID, A.UName,
    SIDS=CAST(MIN(B.SID) as varchar) + CASE 
            WHEN COUNT(*)=1 THEN ''
            ELSE ','+CAST(MAX(B.SID) as varchar)
        END,
    SNames=CAST(MIN(B.SName) as varchar) + CASE 
            WHEN COUNT(*)=1 THEN ''
            ELSE ','+CAST(MAX(B.SName) as varchar)
        END
FROM @A As A, @B As B Where A.UID=B.UID
GROUP BY A.UID, A.UName

/*
(2 行受影响)

(4 行受影响)
UID         UName                SIDS                                                          SNames
----------- -------------------- ------------------------- -------------------------
1           AAA                  1,2                                                           EEE,GGG
2           BBB                  1,2                                                           HHH,QQQ

(2 行受影响)
*/

------解决方案--------------------
SQL code

Declare @A Table(UID Int, UName Varchar(20))
Declare @B Table(SID Int, UID Int, SName Varchar(20))

Insert @A Select 1, 'AAA' Union All Select 2, 'BBB'
Insert @B Select 1, 1, 'EEE' Union All Select 1, 2, 'QQQ' Union All Select 2, 1, 'GGG' Union All Select 2, 2, 'HHH'

SELECT A.UID, A.UName,
    SIDS=CAST(MIN(B.SID) as varchar) + CASE 
            WHEN COUNT(*)=1 THEN ''
            ELSE ','+CAST(MAX(B.SID) as varchar)
        END,
    SNames=CAST(MIN(B.SName) as varchar) + CASE 
            WHEN COUNT(*)=1 THEN ''
            ELSE ','+CAST(MAX(B.SName) as varchar)
        END
FROM @A As A, @B As B Where A.UID=B.UID
GROUP BY A.UID, A.UName
--这个方法比较简单

------解决方案--------------------
SQL code

declare @表A table
(uid int,uname varchar(5))

insert into @表A
select 1, 'aaa' union all
select 2, 'bbb'

declare @表B table
(sid int,uid int, sname varchar(5))

insert into @表B
select 1, 1, 'eee' union all
select 1, 2, 'qqq' union all
select 2, 1, 'ggg' union all
select 2, 2, 'hhh'


select uid,uname,
stuff((select ','+rtrim(sid) from @表B b where b.uid=a.uid for xml path('')),1,1,'') 'sid',
stuff((select ','+sname from @表B b where b.uid=a.uid for xml path('')),1,1,'') 'sname'
from @表A a

/*
uid         uname   sid           sname
----------- ----- ------------- -----------
1           aaa     1,2           eee,ggg
2           bbb     1,2           qqq,hhh

(2 row(s) affected)
*/

------解决方案--------------------
MSSQL2005及以上版本：
SQL code

CREATE TABLE t1
(
    id INT,
    name VARCHAR(10)
)
INSERT INTO t1
SELECT 1, 'aaa' UNION ALL
SELECT 2, 'bbb'
CREATE TABLE t2
(
    id INT,
    tid INT,
    name VARCHAR(10)
)
INSERT INTO t2
SELECT 1, 1, 'eee' UNION ALL
SELECT 1, 2, 'qqq' UNION ALL
SELECT 2, 1, 'ggg' UNION ALL
SELECT 2, 2, 'hhh'
SELECT * FROM t1
SELECT * FROM t2

;WITH aaa AS
(
    SELECT id,STUFF((SELECT ','+LTRIM(tid) FROM t2 AS b WHERE b.id=a.id FOR XML PATH('')),1,1,'') AS tid,
    STUFF((SELECT ','+NAME FROM t2 AS c WHERE c.id=a.id FOR XM