SCCM SQL Query multiple columns aggregation

Question

Hi,
I have the SCCM Following SQL query that returns 1 IP address for each column, I wanted to collect all IPs for a single column (separated by semicolon ) so I don't have machine name duplicates.
Any suggestions?

SELECT
   DISTINCT(CPU.SystemName0) AS [ServerName],
   IPA.IP_Addresses0 AS [IPAddress],
   CS.Manufacturer0 AS [Manufacturer],
   CS.Model0 AS [Model],
   PCB.SerialNumber0 AS [SerialNumber],
   PM.TotalPhysicalMemory0 AS [Memory]
FROM [dbo].[v_GS_PROCESSOR] CPU
   LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM CS
ON CS.ResourceID = CPU.ResourceID
   LEFT OUTER JOIN dbo.v_GS_PC_BIOS PCB
ON CS.ResourceID = PCB.ResourceID
   LEFT OUTER JOIN dbo.v_GS_X86_PC_MEMORY PM
ON CS.ResourceID = PM.ResourceID
   LEFT OUTER JOIN dbo.v_RA_System_IPAddresses IPA
ON CS.ResourceID = IPA.ResourceID
GROUP BY
CPU.SystemName0,
    IPA.IP_Addresses0,
    CS.Manufacturer0,
    CS.Model0,
    PCB.SerialNumber0,
    PM.TotalPhysicalMemory0
ORDER BY [ServerName] ASC

Answer 1

@J.S
Thank you for posting in Microsoft Q&A forum.
Try this query:

SELECT  
    DISTINCT(CPU.SystemName0) AS [ServerName],  
 STUFF((SELECT ', ' + IPA.IP_Addresses0  
          FROM v_RA_System_IPAddresses  IPA  
          WHERE IPA.ResourceID = CPU.ResourceID  
          FOR XML PATH('')), 1, 1, '') [IPAddress],  
    CS.Manufacturer0 AS [Manufacturer],  
    CS.Model0 AS [Model],  
    PCB.SerialNumber0 AS [SerialNumber],  
    PM.TotalPhysicalMemory0 AS [Memory]  
 FROM [dbo].[v_GS_PROCESSOR] CPU  
    LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM CS  
 ON CS.ResourceID = CPU.ResourceID  
    LEFT OUTER JOIN dbo.v_GS_PC_BIOS PCB  
 ON CS.ResourceID = PCB.ResourceID  
    LEFT OUTER JOIN dbo.v_GS_X86_PC_MEMORY PM  
 ON CS.ResourceID = PM.ResourceID  
    LEFT OUTER JOIN dbo.v_RA_System_IPAddresses IPA  
 ON CS.ResourceID = IPA.ResourceID  
 ORDER BY [ServerName] ASC  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you want a comma-separated list of distinct IP addresses, then try something like this:

select string_agg([IPAddress], ', ') as IPAddresses
from
(
    select distinct [IPAddress]
    from
    (
        Your query without ORDER BY
    ) d
) d

Or give details if you need something else. (Maybe show the current and desired results).