Sql-server – Display multiple IP address values for one system in a single row

pivotsql server

I cannot for the life of me get the systems with multiple IPs to display under one row. Below is the current SQL statement I have. systems with more than one IP show up as duplicated on the report. 

SELECT SYS.Netbios_Name0 AS [Computer Name], 
       SYS.User_Name0 AS [Last Login User], 
       SYS.User_Domain0 AS [User Domain], 
       Sys.Resource_Domain_OR_Workgr0 AS [System Domain],  
       v_GS_Computer_System. Manufacturer0 AS [Manufacturer Name],  
       v_GS_COMPUTER_SYSTEM.Model0 AS [Model Name],  
       v_GS_COMPUTER_SYSTEM.SystemType0 AS [System Type],  
       v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [Bios Version],  
       v_GS_PC_BIOS.SerialNumber0 AS [Service Tag],  
       v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [Active Processors],  
       v_RA_System_IPAddresses.IP_Addresses0 AS [IP Addresses]

FROM v_R_SYSTEM SYS
LEFT JOIN  v_GS_COMPUTER_SYSTEM on SYS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID 
LEFT JOIN v_GS_PC_BIOS on  SYS.ResourceID = v_GS_PC_BIOS.ResourceID
LEFT JOIN v_RA_System_IPAddresses on SYS.ResourceID = v_RA_System_IPAddresses.ResourceID

Best Answer

If you're okay with a single column showing the IP addresses as a comma-separated list:

SELECT SYS.Netbios_Name0 AS [Computer Name], 
       SYS.User_Name0 AS [Last Login User], 
       SYS.User_Domain0 AS [User Domain], 
       Sys.Resource_Domain_OR_Workgr0 AS [System Domain],  
       v_GS_Computer_System. Manufacturer0 AS [Manufacturer Name],  
       v_GS_COMPUTER_SYSTEM.Model0 AS [Model Name],  
       v_GS_COMPUTER_SYSTEM.SystemType0 AS [System Type],  
       v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [Bios Version],  
       v_GS_PC_BIOS.SerialNumber0 AS [Service Tag],  
       v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [Active Processors],  
       [IP Addresses] = STUFF((SELECT ',' + rasi.IP_Addresses0
         FROM dbo.v_RA_System_IPAddresses AS rasi
         WHERE rasi.ResourceID = SYS.ResourceID
         FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'')
FROM v_R_SYSTEM SYS
LEFT JOIN  v_GS_COMPUTER_SYSTEM on SYS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID 
LEFT JOIN v_GS_PC_BIOS on  SYS.ResourceID = v_GS_PC_BIOS.ResourceID;

(If you're still getting duplicates here, it's because of the other joins, not the IP addresses.)

If you need each IP address represented under its own column, you'll need to figure out the highest number of IP addresses that exist for any given system, and build a PIVOT statement using dynamic SQL. Not something I can invest in at 5:00 PM and when I don't even know you need it.

Related Question