SQL服务器CONCAT多个值，并返回作为单列

问题描述：

SQL Server 2012的开发版SQL服务器CONCAT多个值，并返回作为单列

TESTMACHINE：

OID MACHINENAME 
--------------- 
1 TEST A 
2 TEST B 
3 TEST C

TESTDETAIL：

OID TESTMACHINEOID TESTID HISTORYID 
------------------------------------- 
1  1   200   100 
2  1   300   250 
3  2   1000  2345 
4  3   100   150

TestMachineOID是FK到TestMachine表

我需要通过分组TestMachineOID将项连接成XML。是否有可能获得以下所需的输出？

TESTMACHINEOID TESTMACHINENAME  TEXTXML 
------------------------------------------------------------------------ 
1     TEST A   <REC><TESTID>200</TESTID><HISTORYID>100<HISTORYID><TESTID>300</TESTID><HISTORYID>250<HISTORYID></REC> 
2     TEST B   <REC><TESTID>1000</TESTID><HISTORYID>2345<HISTORYID></REC> 
3     TEST C   <REC><TESTID>100</TESTID><HISTORYID>150<HISTORYID></REC>

答

drop table #testmachine 
drop table #testdetail 

create table #testmachine (oid int , name nvarchar(10)) 
create table #testdetail (oid int, machineoid int, testid int, historyid int) 

insert into #testmachine (oid, name) values (1,'A') 
insert into #testmachine (oid, name) values (2,'B') 
insert into #testmachine (oid, name) values (3,'C') 

insert into #testdetail (oid, machineoid,testid,historyid) values (1,1,10,20) 
insert into #testdetail (oid, machineoid,testid,historyid) values (2,1,30,40) 
insert into #testdetail (oid, machineoid,testid,historyid) values (3,2,50,60) 
insert into #testdetail (oid, machineoid,testid,historyid) values (4,2,70,80) 
insert into #testdetail (oid, machineoid,testid,historyid) values (5,3,90,100) 


select 
     tm.oid, 
     tm.name, 
     (   
      select testid, historyid 
      from #testdetail td 
      where td.machineoid = tm.oid 
      for xml path('rec') , root('signal') 
     ) as XMLvalue 

from 
     #testmachine tm

输出

1 A <signal><rec><testid>10</testid><historyid>20</historyid></rec><rec><testid>30</testid><historyid>40</historyid></rec></signal> 
2 B <signal><rec><testid>50</testid><historyid>60</historyid></rec><rec><testid>70</testid><historyid>80</historyid></rec></signal> 
3 C <signal><rec><testid>90</testid><historyid>100</historyid></rec></signal>

答

你需要的是一个“for循环”，并用它填充一个表。

循环遍历testmachine表和每个OID选择testdetail所有记录，然后与另一种嵌套for循环与子句添加逐一

SET TESTXML = CONCAT（TESTXML，NEW_VALUE）

SQL服务器CONCAT多个值，并返回作为单列

相关推荐