How to have more than 100 entries in case statement as a variable

I wrote a case statement with > 100 choices where I am using the same statement in 4 places in a simple query.

The same query twice with a union between them but also is doing a count and therefore the group by also contains the case statement.

This is to relabel some company names where different records for the same company are spelled differently.

I tried to declare a variable as a VarChar(MAX)

declare @CaseForAccountConsolidation varchar(max)

SET @CaseForAccountConsolidation = 'CASE 
       WHEN ac.accountName like ''AIR NEW Z%'' THEN ''AIR NEW ZEALAND''
       WHEN ac.accountName LIKE ''AIR BP%'' THEN ''AIR BP''
       WHEN ac.accountName LIKE ''ADDICTION ADVICE%'' THEN ''ADDICTION ADVICE''
       WHEN ac.accountName LIKE ''AIA%'' THEN ''AIA''
       ...

When I went to use it in my select statement - the query just returned the case statement as text and didn't evaluate it.

I also was unable to use it in the group by - I got this error message:

Each GROUP BY expression must contain at least one column that is not an outer reference.

Ideally I would like to have the CASE in just a single place - so that there is no chance of me updating one line and not replicating that elsewhere.

Is there some way of doing this?

I am open to other ways (Like maybe a function - but I am not sure how to use them like this)

Here is a sample of the SELECT I am currently using

```
SELECT
SUM(c.charge_amount) AS GSTExcl
,dl.FirstDateOfMonth AS MonthBilled
,dl.FirstDateOfWeek AS WeekBilled
,CASE
WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
ELSE ac.accountName
END AS accountName
,dl.FinancialYear
,CONVERT(Date,c.date_charged) AS date_charged
FROM [accession] a
LEFT JOIN account_code ac ON a.account_code_id = ac.account_code

Put the data into a table

CREATE TABLE AccountTranslate (wrong VARCHAR(50), translated(VARCHAR(50));

INSERT INTO AccountTranslate VALUES ('ADDICTION ADVICE%','ADDICTION ADVICE');
INSERT INTO AccountTranslate VALUES ('AIR BP%','AIR BP');
INSERT INTO AccountTranslate VALUES ('AIR NEW Z%', 'AIR NEW ZEALAND');

and join to it.

SELECT ...,COALESCE(AccountTranslate.translated, ac.accountName) AS accountName
FROM
...., 
account_code ac left outer join 
AccountTranslate at on ac.accountName LIKE AccountTranslate.wrong

That way you can avoid keeping the data up to date in multiple places. Just use the COALESCE where you need it. You can incorporate this into CTE or VIEWs as per the other suggestions.

StackExchange Database Administrators Q#206832, answer score: 22