SQL- Sorting marketing categories without table data

Question

A department store uses the following table to track sales. The data model does not include information about the floor and the category of the goods been sold.

CREATE TABLE A1
(    
  date      datetime,
  category  nvarchar(30),
  turnover     money  
);

INSERT INTO A1  VALUES   (SYSDATETIME(), 100, 1000);   
INSERT INTO A1  VALUES   (SYSDATETIME(), 201, 1700);
...

Data model/ table cannot be changed or edited. Information on the product group and floor can be diverted from the category.

CREATE VIEW sort_department_productgroups_view
AS
   WITH sort_department_productgroups
        AS (SELECT date,
                   category,
                   turnover,
                   CASE category
                      WHEN 100 THEN 1
                      WHEN 201 THEN 1
                      WHEN 303 THEN 1
                      WHEN 101 THEN 2
                      WHEN 102 THEN 2
                      ELSE 9
                   END
                      floor,
                   CASE category
                      WHEN 100 THEN 'sport'
                      WHEN 102 THEN 'sport'
                      WHEN 201 THEN 'leisure'
                      WHEN 303 THEN 'business'
                      WHEN 101 THEN 'sport'
                      WHEN 202 THEN 'leisure'
                      ELSE 'unknown'
                   END
                      productgroup
              FROM a1)
   SELECT *
     FROM sort_department_productgroups;

go

Example query on the new view:

SELECT * FROM sort_department_productgroups_view where productgroup='sport';

Are there better ways to deal with such a task? Would this work on a big database?

Answer 1

This is really crying for a lookup table. But I must admit, that a category column of type nvarchar filled with numeric values makes me wonder...

Try it like this:

EDIT: Changed the related columen to NVARCHAR(30) as OP mentioned, that this is unchangeable...

CREATE TABLE CATEGORY(ID INT IDENTITY CONSTRAINT PK_CATEGORY PRIMARY KEY
                     ,category NVARCHAR(30)
                     ,CategoryName VARCHAR(100)
                     ,[Floor] INT );
INSERT INTO  CATEGORY VALUES
                      (100,'sport',1)
                     ,(101,'sport',2)
                     ,(102,'sport',2)
                     ,(201,'leisure',1)
                     ,(202,'leisure',9)
                     ,(303,'business',9)
                     --add more...

CREATE TABLE A1
(  
  date      datetime,
  category  NVARCHAR(30),
  turnover     money  
);

INSERT INTO A1  VALUES   (SYSDATETIME(), 100, 1000);   
INSERT INTO A1  VALUES   (SYSDATETIME(), 201, 1700);
GO

CREATE VIEW sort_department_productgroups_view
AS
SELECT A1.date
      ,a1.turnover
      ,ISNULL(CATEGORY.Floor,9) AS [Floor]
      ,ISNULL(CATEGORY.CategoryName,'unknown') AS [productgroup]
FROM A1
LEFT JOIN CATEGORY ON CATEGORY.category=A1.category;
GO

SELECT * FROM sort_department_productgroups_view