Is it possible to PIVOT on a LIKE statement
SUM(CASE
For a limited number of Names you can use a SUM(CASE solution in this way:
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];
PIVOT
If there is an extensive list of Names but only few of them must be rewritten you can maintain the PIVOT solution:
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<>fiddle here
DYNAMIC QUERY
If you feel a bit lazy and don't want to write all column names, you can use a dynamic query:
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<>fiddle here
I think it's important to strictly separate the two tasks you're trying to perform in one step here.
- Classification
- Transformation
For classifying the data, my instinct here is to recommend a lookup table to rigorously map records to a parent class. e.g.
CREATE TABLE StatusType (
ID INT IDENTITY PRIMARY KEY,
[Name] VARCHAR(10) NOT NULL UNIQUE
);
GO
ALTER TABLE [Status]
ADD StatusTypeID INT NOT NULL
DEFAULT 1
FOREIGN KEY REFERENCES StatusType (ID) ;
...where the seed record in StatusType
(ID
=1 for the Status.StatusTypeID
default) is a placeholder record named "Unknown" or similar.
When the lookup data is seeded and base records are updated with the correct keys, you can pivot to your heart's content.
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;
Full dbfiddle