Is it possible to group by elements (as in COLUMN LIKE='Value%') in a PIVOT table? I have a table [DBT].[Status] which contains various statuses (of databases, instances, etc.) and don't want to pivot/query all the PROD and TEST values as single values, but group them.

E.g. Instead of having columns for the statuses Prod, Prod ACC, Prod APP, .. etc. I would have only one column containing the values for Name LIKE 'Prod%' and Name LIKE 'Test%'.

What I have so far:

Table Definition

CREATE TABLE [DBT].[Status](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [IX_Status] UNIQUE NONCLUSTERED 
(
 [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

Table Values

INSERT INTO [DBT].[Status]
(
 -- ID -- this column value is auto-generated
 Name
)
VALUES
('Test ACC'),
('Test APP'),
('Test DBA'),
('Prod ACC'),
('Prod APP'),
('Prod DBA'),
('Prod'),
('Test'),
('Migrated'),
('Offline'),
('Reserved')

The Pivoted Status Table

SELECT 'Database Status' AS [DB Status], 
[1] AS [Test ACC], [2] AS [Test APP], [3] AS [Test DBA], [4] AS [Prod ACC], [5] AS [Prod APP], [6] AS [Prod DBA], [7] AS [Prod], [8] AS [Test], [9] AS [Migrated], [10] AS [Offline], [11] AS [Reserved] 
FROM 
(
 SELECT ID, Name FROM [DBT].[Status]
) AS Source
PIVOT
(
 COUNT(Name) FOR ID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable

Output So Far

DB Status Test ACC Test APP Test DBA Prod ACC Prod APP Prod DBA Prod Test Migrated Offline Reserved
--------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Database Status 1 1 1 1 1 1 1 1 1 1 1

db<>fiddle

The dbfiddle so far.

Question

Instead of having multiple rows for the various Test... and Prod.... values, I would prefer to have them grouped, similar to the following:

DB Status | Test | Prod | Migrated | Offline | Reserved 
--------------- | ---- | ---- | -------- | ------- | --------
Database Status | 4 | 4 | 1 | 1 | 1

I don't have any clue how to go about solving my question. (To be honest I only just grasped PIVOT yesterday after extensive trial and errors).

This question is loosely related to the question How to create sums/counts of grouped items over multiple tables I have already asked. The tables [DBT].[Instance] and [DBT].[Database] contain a column with the [StatusID] which corresponds to the table we are looking at now.

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.

1. Classification


2. 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