SQL Server 2014

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],
[1] AS [Test ACC], [2] AS [Test APP], [3] AS [Test DBA], [4] AS [Prod ACC], [5] AS [Prod APP], [6] AS [Prod DBA], [7] AS [Prod], [8] AS [Test], [9] AS [Migrated], [10] AS [Offline], [11] AS [Reserved]
FROM
(
	SELECT ID, Name  FROM [Status]
) AS Source
PIVOT
(
	COUNT(Name) FOR ID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable


DB Status
Test ACC
Test APP
Test DBA
Prod ACC
Prod APP
Prod DBA
Prod
Test
Migrated
Offline
Reserved


Database Status
1
1
1
1
1
1
1
1
1
1
1