Identify items and convert row to column in SQL-CodePudding

I'm trying to identify any other items produced by employees besides "shoe" for any given month. Using my codes, the entire row(s) are removed as long as the employee produces "shoe" at that month. Any help would be greatly appreciated!.

Her is the sample data:

create table tbl
(
  Employee  char,
  Month     varchar (6),
  Monday    varchar (6),
  Tuesday   varchar (6),
  Wednesday varchar (6),
);

insert into tbl values 
('A',       'May'  ,    'Shoe',   '',        'Cloth'),
('A',       'March',    'Belt',   'Cloth',   'Hat'),
('A',       'July',     'Belt',   '',        ''),
('B',       'April',    'Shoe',   ''  ,      'Hat'),
('A',       'June',      '',      ''  ,      'Mask'),
('B',       'May',      'Shoe',   ''  ,      ''),
('C',       'March',    'Hat',    '',        'Shoe');

Here is the codes I used:

    select * 
    from   tbl
   where Monday not in ('Shoe') 
   and Tuesday not in ('Shoe') 
  and Wednesday not in ('Shoe')

Here is the expected outcomes:

CodePudding user response：

You can simplify this a bit via a CROSS APPLY to UNPIVOT your data

Example

Select Employee
      ,Month
      ,B.*
 From  tbl A
 Cross Apply ( values ('Monday'   ,Monday   )
                     ,('Tuesday'  ,Tuesday  )
                     ,('Wednesday',Wednesday)
             ) B(Day,Item)
 Where Item not in ('Shoe','')

Results

EDIT - Just Another Option

If you want to UNPIVOT your data without Dynamic SQL or enumerating all the columns (if many or variable), you can use a bit of JSON

Select Employee
      ,Month
      ,Day = B.[Key]
      ,Item = B.Value
 From  tbl A
 Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] not in ('Employee','Month')
                  and Value not in ('Shoe','')
             ) B

CodePudding user response：

SELECT Employee,Month,'Monday' AS "Day",Monday AS "Item" FROM tbl WHERE Monday NOT IN ('Shoe','')
UNION ALL
SELECT Employee,Month,'Tuesday' AS "Day",Tuesday AS "Item" FROM tbl WHERE Tuesday NOT IN ('Shoe','')
UNION ALL
SELECT Employee,Month,'Wednesday' AS "Day",Wednesday AS "Item" FROM tbl WHERE Wednesday NOT IN ('Shoe','')