Return values as 1 or 0 together with percent I need a SELECT that will result in a percentage and columns with 1 and 0 for exist or not exist.

The goal is to generate data for a usage meter (Percentage 0.0-1.0) and each measurable value as 1 or 0.
It also has to show a row for every user in a users table, even if that user has no items at all.

Scenario:


In one table, a user can have many items, thet all have a specific ItemType.
Like type A, B, C, D or E
If a user has items in all types, the result should be (5/5 100%):
Percentage  A  B  C  D  E
1.0, 1, 1, 1, 1, 1
If a user has none of the types (0/5 0%):
Percentage  A  B  C  D  E
0.0, 0, 0, 0, 0, 0
If a user has some of the types (3/5 60%):
Percentage  A  B  C  D  E
0.6, 1, 1, 1, 0, 0
SAMPLE:
Use a CTE to calculate A,B,C,D and E using CASE or IIF and then calculate final percentatge.




;WITH tbl AS
(
SELECT 
    u.id,
    u.name,
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'A') 
         THEN 1 ELSE 0 END AS [A],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'B') 
         THEN 1 ELSE 0 END AS [B],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'C') 
         THEN 1 ELSE 0 END AS [C], 
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'D') 
         THEN 1 ELSE 0 END AS [D],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'E') 
         THEN 1 ELSE 0 END AS [E]
FROM
    users u
)
SELECT
    id,
    name,
    CAST((A+B+C+D+E) / 5.0 as decimal(10,1)) AS Percentatge,
    A,
    B,
    C,
    D,
    E
FROM
    tbl;
GO



id | name  | Percentatge |  A |  B |  C |  D |  E
-: | :---- | :---------- | -: | -: | -: | -: | -:
 1 | user1 | 1.0         |  1 |  1 |  1 |  1 |  1
 2 | user2 | 0.4         |  1 |  1 |  0 |  0 |  0
 3 | user3 | 0.2         |  0 |  1 |  0 |  0 |  0
 4 | user4 | 0.0         |  0 |  0 |  0 |  0 |  0
 5 | user5 | 0.0         |  0 |  0 |  0 |  0 |  0



db&lt;>fiddle here