SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (`UserID` INTEGER, `Category` VARCHAR(1));
INSERT INTO tablename (`UserID`, `Category`) VALUES
  ('1', 'a'),
  ('1', 'b'),
  ('2', 'c'),
  ('3', 'b'),
  ('3', 'a'),
  ('3', 'c');
WITH 
  users AS (SELECT DISTINCT UserID FROM tablename),
  categories AS (
    SELECT DISTINCT Category, DENSE_RANK() OVER (ORDER BY Category) rn
    FROM tablename
  ),
  cte AS (
    SELECT u.UserID, c.rn,
           '"' || GROUP_CONCAT(t.UserID IS NOT NULL)
                  OVER (PARTITION BY u.UserID ORDER BY c.rn) || '"' encoding
    FROM users u CROSS JOIN categories c
    LEFT JOIN tablename t
    ON t.UserID = u.UserID AND t.Category = c.Category
  )
SELECT DISTINCT userID, 
       FIRST_VALUE(encoding) OVER (PARTITION BY UserID ORDER BY rn DESC) encoding
FROM cte
ORDER BY userID


UserID
encoding


1
"1,1,0"

2
"0,0,1"

3
"1,1,1"