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],
SUM(CASE WHEN Name LIKE 'Test%' THEN 1 ELSE 0 END) As Test,
SUM(CASE WHEN Name LIKE 'Prod%' THEN 1 ELSE 0 END) AS Prod,
SUM(CASE WHEN Name = 'Migrated' THEN 1 ELSE 0 END) AS Migrated,
SUM(CASE WHEN Name = 'Offline' THEN 1 ELSE 0 END) AS Offline,
SUM(CASE WHEN Name = 'Reserved' THEN 1 ELSE 0 END) AS Reserved
FROM
[Status];
| DB Status | Test | Prod | Migrated | Offline | Reserved |
|---|---|---|---|---|---|
| Database status | 4 | 4 | 1 | 1 | 1 |
SELECT 'Database Status' AS [DB Status],
[Test], [Prod], [Migrated], [Offline], [Reserved]
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 ([Test], [Prod], [Migrated], [Offline], [Reserved])
) AS PivotTable
| DB Status | Test | Prod | Migrated | Offline | Reserved |
|---|---|---|---|---|---|
| Database Status | 4 | 4 | 1 | 1 | 1 |