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

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(CASE WHEN Name LIKE 'Test%' THEN 'Test'
	                                                WHEN Name LIKE 'Prod%' THEN 'Prod'
	                                                ELSE Name END)
                   FROM [Status]
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd = 
'SELECT ''Database Status'' AS [DB Status],' + @cols + ' 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 (' + @cols + ')
) PVT'

EXEC(@cmd);


DB Status
Migrated
Offline
Prod
Reserved
Test


Database Status
1
1
4
1
4