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.

Below is a very simplified version of my existing query plus what I want to query -- the combined location that doesn't exist yet.

SELECT Date, Location, Count(VisitID)
FROM database
WHERE Location = LOC1
UNION ALL
-- repeat for LOC2-5 
UNION ALL
SELECT Date, Location, VisitID
FROM database
WHERE Location = LOC3 4

Expected Results (includes combined location with expected result)

CodePudding user response：

You can use a conditional expression to assign the new location. In SQL Server, it is handy to do this in cross apply :

select t.date, x.new_location, count(*) cnt
from mytable t
cross apply ( values 
    (case when location in ('LOC3', 'LOC4') then 'LOC3-4' else location end)
) x(new_location)
group by t.date, x.new_location

CodePudding user response：

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:

(using abbreviated test data)

See this db<>fiddle for a demo.