I have 2 SQL queries and I need to combine them together in one piece-CodePudding

I need help with combining two queries which gives out different results into one functional query.

use DBname
select tblComputer.HostName, 
    tblComputer.Manufacturer, 
    tblComputer.Model, 
    tblComputerHardware.ProcessorType, 
    tblComputerHardware.ProcessorCount, 
    tblComputerHardware.CoreCount, 
    tblInstances.InstanceName
from tblDatabases 
    JOIN tblComputer ON tblDatabases.ComputerID=tblComputer.ComputerID 
    join tblComputerHardware ON tblComputer.ComputerID=tblComputerHardware.ComputerID 
    join tblInstances ON tblComputer.ComputerID=tblInstances.ComputerID 
where IsVirtual=0

So this query gives out results which I defined from couple of tables. Now I want to add one more column in which I will have Instances installed on each computer. Note that there are multiple instances on one computer and I need those instances to be seperated with ",". I have found that this query works for me

select tblInstances = STUFF((
    select ','   InstanceName
    from tblInstances
    for xml path ('')
    ), 1, 1, '')

But I`ve been struggling on how to combine theese two queries in one so that I would be able to see multiple instances seperated with comma on each computer.

Im hoping for help from you guys and I hope that you will understand my issue.

CodePudding user response：

You need to use a lateral subquery. I've assumed that you've used FOR XML PATH intentionally because you're on SQL Server 2016 or prior, and so have also used that. If you are on 2017 then you probably want to look into STRING_AGG. I have, however, switched to using TYPE and text() as the former won't escape special characters, and the latter is more performant:

SELECT C.HostName, 
       C.Manufacturer, 
       C.Model, 
       CH.ProcessorType, 
       CH.ProcessorCount, 
       CH.CoreCount, 
       STUFF((SELECT ','   I.InstanceName
              FROM tblInstances I
              WHERE I.ComputerID = C.ComputerID
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'') AS InstanceNames --Use nvarchar if needed
FROM tblDatabases D --Use Aliases, it makes your code far more succinct
    JOIN tblComputer C ON D.ComputerID = C.ComputerID 
    JOIN tblComputerHardware CH ON C.ComputerID = CH.ComputerID 
WHERE C.IsVirtual = 0;  --Alias guessed

CodePudding user response：

You mean like this ?

select tblComputer.HostName, 
    tblComputer.Manufacturer, 
    tblComputer.Model, 
    tblComputerHardware.ProcessorType, 
    tblComputerHardware.ProcessorCount, 
    tblComputerHardware.CoreCount, 
    string_agg(tblInstances.InstanceName, ',') as Instances
from tblDatabases 
    JOIN tblComputer ON tblDatabases.ComputerID=tblComputer.ComputerID 
    join tblComputerHardware ON tblComputer.ComputerID=tblComputerHardware.ComputerID 
    join tblInstances ON tblComputer.ComputerID=tblInstances.ComputerID 
where IsVirtual=0
group by tblComputer.HostName, 
    tblComputer.Manufacturer 
    tblComputer.Model, 
    tblComputerHardware.ProcessorType, 
    tblComputerHardware.ProcessorCount, 
    tblComputerHardware.CoreCount