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'),('Prod ACC'),
('Test APP'),('Prod APP'),
('Test DBA'),('Prod DBA'),
('Test'),    ('Prod'),
('Migrated'),('Offline'),('Reserved');
11 rows affected

CREATE TABLE StatusType (
  ID INT IDENTITY PRIMARY KEY,
  [Name] VARCHAR(10) NOT NULL UNIQUE
);
INSERT StatusType ([Name])
VALUES
('Unknown'),
('Test'),
('Prod'),
('Migrated'),
('Offline'),
('Reserved');
6 rows affected

ALTER TABLE [Status] 
    ADD StatusTypeID INT NOT NULL 
        DEFAULT 1
        FOREIGN KEY REFERENCES StatusType (ID) ;
-- but, you know... better assignment logic here... 
WITH ids AS (
    SELECT s.StatusTypeID AS old_id
          ,st.ID AS new_id
    FROM [Status] AS s
    OUTER APPLY ( 
        SELECT TOP(1) st.ID
        FROM StatusType AS st
        WHERE PATINDEX('%'+st.[Name]+'%',s.[Name]) > 0
    ) AS st
)
UPDATE ids 
SET old_id = new_id;
11 rows affected

select 'Database Status' AS [DB Status],
    [Test], [Prod], [Migrated], [Offline], [Reserved]
from (
    select s.ID,
           st.Name as StatusTypeName
    from status s
    join statusType st on st.ID = s.StatusTypeID
) as Source
pivot (
    count(ID) for StatusTypeName in ([Test],[Prod],[Migrated],[Offline],[Reserved],[Unknown])
) as pvt;


DB Status
Test
Prod
Migrated
Offline
Reserved


Database Status
4
4
1
1
1