--Create table T1(Tid int identity(1,1) primary key,
--TName Nvarchar(50)
--)--Create Table T2(id int identity(1,1) primary key,
--Tid int default 0 not null,
--ItemName Nvarchar(10),
--ItemResult Nvarchar(2)
--)--insert into T1 values(N'大田工程')--insert into T2 select 1,N'病好了',N'是'
--union all select 1,N'人数多吗',N'否'
;WITH ym AS
(
SELECT t1.tid,t1.tname,t2.Itemname+':'+t2.itemresult AS Content
FROM t1 INNER JOIN t2 ON t1.tid=t2.tid
)
select a.tid,a.tname,
stuff((select ','+Content from ym b
where b.tid=a.tid and b.tname=a.tname
for xml path('')),1,1,'') 'Content'
from ym a
group by a.tid,a.tname/*
tid tname Content
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 大田工程 病好了:是,人数多吗:否*/
ItemResult = STUFF(( SELECT ',' +ItemName+':'+ ItemResult
FROM T2 t
WHERE tid = T2.tid
FOR
XML PATH('')
), 1, 1, '')
FROM T2
INNER join T1 ON T2.tid = T1.tid
GROUP BY T2.tid,T1.TName/*
tid TName ItemResult
1 大田工程 病好了:是,人数多吗:否*/