Having difficulty with nested select statements in SQL

Question

I'm trying to create a ConfigMgr report that will display 3 registry key values for each laptop in our environment. My query looks like this:

Select distinct

SYS.Netbios_Name0,
(Select REG.Value0 where REG.KeyPath0='HKEY_LOCAL_MACHINE\SOFTWARE\BatteryDesignCapacity' ) as BatteryDesignCapacity,
(Select REG.Value0 where REG.KeyPath0='HKEY_LOCAL_MACHINE\SOFTWARE\BatteryFullChargedCapacity' ) as BatteryFullChargedCapacity,
(Select REG.Value0 where REG.KeyPath0='HKEY_LOCAL_MACHINE\SOFTWARE\BatteryHealth' ) as BatteryHealth

from v_GS_Registry_Values0 REG

INNER JOIN v_R_System_Valid SYS
ON SYS.ResourceID = REG.ResourceID

ORDER BY SYS.Netbios_Name0

However when I run the report, I am getting 4 rows for each laptop name. One row contains the laptop name and all blank registry values, the next row contains only the BatteryDesignCapacity value, the third row contains only BatteryFullChargedCapacity and the fourth row only BatteryHealth.

What am I doing wrong? Is it possible to put all 4 registry values into a single row by reworking the select statements?

score 1 · Answer 1 · answered May 04 '21 at 19:26

I think you want conditional aggregation:

SELECT SYS.Netbios_Name0,
       MAX(CASE WHEN REG.KeyPath0 = 'HKEY_LOCAL_MACHINE\SOFTWARE\BatteryDesignCapacity' THEN REG.Value0  END) as BatteryDesignCapacity,
       MAX(CASE WHEN REG.KeyPath0 = 'HKEY_LOCAL_MACHINE\SOFTWARE\BatteryFullChargedCapacity' THEN REG.Value0 END) as BatteryFullChargedCapacity,
       MAX(CASE WHEN REG.KeyPath0 ='HKEY_LOCAL_MACHINE\SOFTWARE\BatteryHealth' THEN REG.Value0 END) as BatteryHealth
FROM v_GS_Registry_Values0 REG INNER JOIN
     v_R_System_Valid SYS
     ON SYS.ResourceID = REG.ResourceID
GROUP BY SYS.Netbios_Name0
ORDER BY SYS.Netbios_Name0

score 0 · Answer 2 · answered May 04 '21 at 19:25

It would appear v_GS_Registry_Values0 has multiple rows for each computer. You could try this, or use the SQL PIVOT function.

SELECT  DISTINCT
        SYS.Netbios_Name0
        ,MAX(   CASE
                    WHEN REG.KeyPath0 = 'HKEY_LOCAL_MACHINE\SOFTWARE\BatteryDesignCapacity' THEN REG.Value0
                END
            ) AS BatteryDesignCapacity
        ,MAX(   CASE
                    WHEN REG.KeyPath0 = 'HKEY_LOCAL_MACHINE\SOFTWARE\BatteryFullChargedCapacity' THEN REG.Value0
                END
            ) AS BatteryFullChargedCapacity
        ,MAX(   CASE
                    WHEN REG.KeyPath0 = 'HKEY_LOCAL_MACHINE\SOFTWARE\BatteryHealth' THEN REG.Value0
                END
            ) AS BatteryHealth
FROM    v_GS_Registry_Values0 AS REG
        INNER JOIN v_R_System_Valid AS SYS ON SYS.ResourceID = REG.ResourceID
GROUP BY
        SYS.Netbios_Name0;

Having difficulty with nested select statements in SQL

2 Answers2