Is it possible to PIVOT on a LIKE statement

sql-server sql-server-2014 add tag

John K N (imported from SE)

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][L2] 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][L1]** 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.*


  [L1]: https://dba.stackexchange.com/q/163577/15356
  [L2]: https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=c65a0d71de94fcf675301efe4078ae2a

Top Answer

PeterVandivier (imported from SE)

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.

```sql
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.

```sql
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][1]

[1]: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=15c0a2eec15497f57be0c9f19a73c61a

Answer #2

McNets (imported from SE)

**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](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=425fac8c5421fef07d33c301caa7e199)*

**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](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=cefd5b16562b37968fd6cda43978400e)*

2 Answers