Made this adjustment but it is spitting out way more IP's than it should
, (SELECT T693.C200000020 + ',' AS 'data()'
LEFT JOIN T693 AS I ON T712.C179 = I.C400131200
FOR XML PATH(''), TYPE)
FROM T712 WHERE T712.C200000020 = 'server.name'
AND T712.C400127400 = 'BMC.ASSET'
35 reviews and no responses. Surely somebody has an idea.
Would you please share the form names of 712 and 693 and field name of the ids used in Query?
Also how you want the output?
If you can mock the output would be fine.
2 of 2 people found this helpful
For anybody needing to do this. This is the query I got to work. This is from Computer System and ProtocolEndpoint forms where you want to pull 1 computer system and the related IP addresses into a single row of data with IP's being in a comma separated list.
T712 is the Computer System form and T693 is the Protocol Endpoint form.
SELECT DISTINCT ST2.C200000020 AS Name,
SELECT ','+ST1.C200000020 AS [text()]
FROM dbo.T693 ST1
WHERE ST1.C400131200 = ST2.C179 AND ST1.C301141500 = '2'
ORDER BY ST1.C179
FOR XML PATH ('')
), 2, 1000) [IPs]
FROM dbo.T712 ST2 WHERE ST2.C400127400 = 'BMC.Asset'