SQL Server Subquery – Return New Row and Fill One Column

sql server

I'm trying to fetch information about a database and I have the following query that prints most of it out:

SELECT Serverproperty('ServerName') AS [Server], 
       (SELECT Server_type = CASE 
                               WHEN virtual_machine_type = 1 THEN 'Virtual' 
                               ELSE 'Physical' 
                             END 
        FROM sys.dm_os_sys_info)
       AS [Virtual/Physical], 
       (SELECT ( cpu_count / hyperthread_ratio ) 
        FROM   sys.dm_os_sys_info) 
       AS [CPU(s)], 
       (SELECT CASE 
                 WHEN hyperthread_ratio = cpu_count THEN cpu_count 
                 WHEN ( ( cpu_count - hyperthread_ratio ) / ( 
                        cpu_count / hyperthread_ratio ) 
                      ) = 0 THEN 1 
               END  
        FROM   sys.dm_os_sys_info) 
       AS [Core(s)], 
       Substring ((SELECT @@VERSION), 1, Charindex('-', (SELECT @@VERSION)) - 1) 
       AS [Product Name], 
       Serverproperty('ProductVersion') AS [Product Version], 
       Serverproperty('ProductLevel') AS [Product Level], 
       Serverproperty('Edition') AS [Edition] 

Result:
Results

This is where my issue comes in. I found a script that prints out all your instances on the server, too. Like this:

DECLARE @GetInstances TABLE 
  ( 
     value         NVARCHAR(100), 
     instancenames NVARCHAR(100), 
     data          NVARCHAR(100) 
  ) 

INSERT INTO @GetInstances 
EXECUTE Xp_regread 
  @rootkey = 'HKEY_LOCAL_MACHINE', 
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server', 
  @value_name = 'InstalledInstances' 

SELECT instancenames 
FROM   @GetInstances 

Instance Names

My issue is that I cannot seem to forge the first query with the second by including it in the SELECT-statement of the first script. I get the error:

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

How can I fix this?
I would like the result to be printed out in the form of the image below, is that possible?
Desired result

Best Answer

Only way to format like that is to UNION the queries:

DECLARE @GetInstances TABLE 
  ( 
     value         NVARCHAR(100), 
     instancenames NVARCHAR(100), 
     data          NVARCHAR(100) 
  ) 

INSERT INTO @GetInstances 
EXECUTE Xp_regread 
  @rootkey = 'HKEY_LOCAL_MACHINE', 
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server', 
  @value_name = 'InstalledInstances' 


SELECT Serverproperty('ServerName') AS [Server], 
       (SELECT Server_type = CASE 
                               WHEN virtual_machine_type = 1 THEN 'Virtual' 
                               ELSE 'Physical' 
                             END 
        FROM sys.dm_os_sys_info)
       AS [Virtual/Physical], 
       CAST((SELECT ( cpu_count / hyperthread_ratio ) 
        FROM   sys.dm_os_sys_info) 
        AS VARCHAR(10))
       AS  [CPU(s)], 
       CAST((SELECT CASE 
                 WHEN hyperthread_ratio = cpu_count THEN cpu_count 
                 WHEN ( ( cpu_count - hyperthread_ratio ) / ( 
                        cpu_count / hyperthread_ratio ) 
                      ) = 0 THEN 1 
               END  
        FROM   sys.dm_os_sys_info) 
         AS VARCHAR(10)) AS [Core(s)], 
       Substring ((SELECT @@VERSION), 1, Charindex('-', (SELECT @@VERSION)) - 1) 
       AS [Product Name], 
       Serverproperty('ProductVersion') AS [Product Version], 
       Serverproperty('ProductLevel') AS [Product Level], 
       Serverproperty('Edition') AS [Edition] 

UNION ALL

SELECT '', instancenames, '', '', '', '', '', ''
FROM   @GetInstances 

We return blank strings '' for most columns in the second query as both queries must have the same number of columns. Note I've also CAST [CPU(s)] and [Core(s)] columns in the top query to VARCHAR, otherwise the second query returns 0 in those columns ('' cast to INT = 0).