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

Please provide sample data, desired results, and an appropriate database tag. — Gordon Linoff, Dec 23 '20 at 12:24

score 0 · Answer 1 · answered Dec 23 '20 at 13:21

0

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

answered Dec 23 '20 at 13:21

Reza Esmaeli

146
7

Please add some description on the solution provided. The code can ben self explanatory only to you and maybe the recipient but the thread can be read by many other people who may not understand clearly without hints. – weirdgyn Dec 23 '20 at 13:58
I only wanted to concatenate the IPAddress in a single column – J_PT Dec 23 '20 at 15:54

SCCM / SQL Multiple values to one column

1 Answers1