sql - SCCM / SQL Multiple values to one column

Question

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?

the goal is to return

ServerName IPAddress
Server01 192.168.1.1;192.168.1.2;fe80::61d2:9748:f21f:d4f3

    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

标签： sqlsccm

Answer 1

SELECT CONCAT(
   CPU.SystemName0,
   ';',
   min(IPA.IP_Addresses0),
   ';',
   min(CS.Manufacturer0),
   ';',
   min(CS.Model0),
   ';',
   min(PCB.SerialNumber0),
   ';',
   min(PM.TotalPhysicalMemory0))
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
ORDER BY CPU.SystemName0 ASC