SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [Status]
(
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](50) NOT NULL,
  CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED ([ID] ASC),
  CONSTRAINT [IX_Status] UNIQUE NONCLUSTERED ([Name] ASC) 
);
INSERT INTO [Status] (Name) VALUES
('Test ACC'),('Test APP'),('Test DBA'),('Prod ACC'),('Prod APP'),('Prod DBA'),('Prod'),('Test'),('Migrated'),('Offline'),('Reserved');
11 rows affected

SELECT 
    'Database status' as [DB Status],
    SUM(CASE WHEN Name LIKE 'Test%' THEN 1 ELSE 0 END) As Test,
    SUM(CASE WHEN Name LIKE 'Prod%' THEN 1 ELSE 0 END) AS Prod,
    SUM(CASE WHEN Name = 'Migrated' THEN 1 ELSE 0 END) AS Migrated,
    SUM(CASE WHEN Name = 'Offline' THEN 1 ELSE 0 END) AS Offline,
    SUM(CASE WHEN Name = 'Reserved' THEN 1 ELSE 0 END) AS Reserved
FROM 
    [Status];


DB Status
Test
Prod
Migrated
Offline
Reserved


Database status
4
4
1
1
1


SELECT 'Database Status' AS [DB Status],
[Test], [Prod], [Migrated], [Offline], [Reserved]
FROM
(
	SELECT 
	    ID, 
	    CASE
	        WHEN Name LIKE 'Test%' THEN 'Test'
	        WHEN Name LIKE 'Prod%' THEN 'Prod'
	        ELSE Name
	    END AS Name
	FROM 
	    [Status]
) AS Source
PIVOT
(
	COUNT(ID) FOR Name IN ([Test], [Prod], [Migrated], [Offline], [Reserved])
) AS PivotTable


DB Status
Test
Prod
Migrated
Offline
Reserved


Database Status
4
4
1
1
1