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 |