Join on hardcoded values on table built inline

Question

I'm using SQL Server '16.

I've built the following inline table:

   SELECT * FROM 
   (VALUES('ALPL', 'cat1'),
     ('APLM', 'cat1'),
     ('BATA', 'cat2'),
     ('BPL', 'cat3'),
     ('LOF', 'cat4'),
     ('FLSH', 'cat5'),
     ('BPL', 'cat5'),
     ('FEPL', 'cat5'),
     ('MPL', 'cat5'),)
   AS categories(groupcd, Category)

and need to join categories to the results from groupcd from this existing query:

   SELECT 
   Storeno
  ,groupcd
  ,Date
  ,Sum([TEXTEND]) as Sales
   FROM [DB ].[dbo].[DTL] DTL 
   join
   ICDB..HDR HDR on HDR.Orderno = DTL.OrderNo
   where groupcd in('ACPL', 'ADV',  'ALPL', 'APLM', 'BATA', 'BOSU', 'BOT',  'BPL',  'FARM', 'FEPL', 'FLSH', 'LOF',  'MAST', 'MBT',  'MPL',  'MSPL', 'OTT',  'SNT',  'SPE',  'SSL',  'TIRL', 'TIRM', 'TPMS')
   and  Date>= convert(datetime,'01/01/2018') 
   group by Storeno, Date, groupcd

I don't know how to join to the table since it doesn't exist in a DB.

I need 'Category' field from 1st table in the results from 2nd table.

Thank you,

Answer 1

Use a CTE:

WITH categories(groupcd, Category) as (
      SELECT * 
      FROM (VALUES('ALPL', 'cat1'),
     ('APLM', 'cat1'),
     ('BATA', 'cat2'),
     ('BPL', 'cat3'),
     ('LOF', 'cat4'),
     ('FLSH', 'cat5'),
     ('BPL', 'cat5'),
     ('FEPL', 'cat5'),
     ('MPL', 'cat5')
       ) AS categories(groupcd, Category)
    )
SELECT . . 
FROM categories c . . .