Create table T1(Tid int identity(1,1) primary key,
TName varchar(50)
)
Create Table T2(id int identity(1,1) primary key,
Tid int default 0 not null,
ItemName varchar(10),
ItemResult varchar(2)
)
insert into T1 values(‘大田工程’)
insert into T2 select 1,'病好了','是'
union all select 1,'人数多吗','否'
用一条查询语句，最终显示如下：
Tid TName Content
1 大田工程 病好了：是,人数多吗：否
就是将T2查询结果合并后，放入一个字段显示
------解决方案--------------------
--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 大田工程 病好了:是,人数多吗:否
*/
------解决方案--------------------
SELECT T2.tid ,T1.TName,
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 大田工程 病好了:是,人数多吗:否*/