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 |