T-SQL Query - how to create a new temporary "location" in a table under a location column that combines other location data

Question

While running a query that selects a Location, date, and visitIDs, I need to be able to select a new combined location name (eg. "LOC3+4") which includes all the rows from locations "LOC3" and "LOC4". This new dataset need to have its own location name so that I can group the data by Location in an SSRS report.

Simplified Query - EDITED to add joins and subqueries-- the combined location that doesn't exist yet.

SELECT subq1.Date, subq1.Location, Count(subq1.VisitID)
FROM 
    (SELECT t1.Date, t1.Location, t1.VisitID 
        FROM table t1 LEFT OUTER JOIN 
        table t2 ON t2.VisitID = t1.VisitID LEFT OUTER JOIN
        table t3 ON t3.ClientID = t2.ClientID
        (etc)
    WHERE t1.FacID = FAC1 AND t1.Status = 'Adm') subq1 
WHERE subq1.Location = 'LOC1' AND subq1.Room NOT IN ('Room1','Room2',etc....)

UNION ALL
-- repeat for LOC2-5 

UNION ALL
(not sure how to do the combined location)

Expected Results (includes combined location with expected result)

Answer 1

To Expand on GMB's answer, I think you need one or more UNIONS in the cross apply to include both the original and the mapped locations.

select t.date, x.location, count(*) cnt
from mytable t
cross apply (
    select location
    union all
    select 'LOC3-4' AS location where t.location in ('LOC3', 'LOC4')
    union all
    select 'LOC2-5' AS location where t.location in ('LOC2', 'LOC3', 'LOC4', 'LOC5')
) x
group by t.date, x.location

A better solution may be to use a table driven mapping that associates original locations with mapped/combined locations.

declare @mapping table (location varchar(10), mapped_location varchar(10))
insert @mapping
values
    ('LOC3', 'LOC3-4'),('LOC4', 'LOC3-4'),
    ('LOC2', 'LOC2-5'),('LOC3', 'LOC2-5'),('LOC4', 'LOC2-5'),('LOC5', 'LOC2-5')

select t.date, x.location, count(*) cnt
from mytable t
cross apply (
    select location
    union all
    select m.mapped_location as location
    from @mapping m
    where m.location = t.location
) x
group by t.date, x.location

If there are no matching entries, you only count the original location. The mapping can also define multiple mapped locations for a single source location.

Both of the above generate results like:

date	location	cnt
2022-10-31	LOC1	1
2022-10-31	LOC2	2
2022-10-31	LOC2-5	14
2022-10-31	LOC3	3
2022-10-31	LOC3-4	7
2022-10-31	LOC4	4
2022-10-31	LOC5	5

(using abbreviated test data)

See this db<>fiddle for a demo.