Best way to count distinct occurrences across 3 columns-CodePudding

I am looking for the best way to count the number of unique users that have interacted with an item using SQL, however I'm not sure the best way to go about this.

To start with, here is an example of my data.

ItemID    State1    State2    State3    (DesiredResult)
--------------------------------------------------------
1         User1     User1     User1     1
2         User1     User1     User2     2
3         User1     User2     User3     3
4         User1     User2     User1     2

To explain, as an item progresses from state to state, it can be progressed by any user. What I could like to do for each item, is get the number of unique users that have interacted with the item by progressing the status at some point (for reference, I've added the desired output to the data above)

Now I know that this would be possible using a CASE statements, checking for each condition using something like this

SELECT
ItemID
,CASE   WHEN State1 = State2 AND State1 = State3 THEN 1
        WHEN State1 = State2 AND State1 <> State3 THEN 2
        WHEN State1 <> State2 AND State1 = State3 THEN 2
        WHEN State1 <> State2 AND State1 <> State3 AND State2 = State3 THEN 2
        WHEN State1 <> State2 AND State1 <> State3 AND State2 <> State3 THEN 3 
END AS UserCount

However that seems a little cumbersome, so I'm just wondering whether there is another, more efficient / stream-lined way to achieve what I'm after.

Any advise would be appreciated.

CodePudding user response：

The shape of this table may be nice for reporting but makes both recording and querying state transitions harder. In the relational model data flows along rows, not columns.

You can UNPIVOT the data into a ItemID, State, User shape and then count the distinct Users.

with unpvt as 
(
    SELECT ItemId, State, User
    FROM   
       (SELECT ItemID, State1, State2, State2
       FROM someTable) p  
    UNPIVOT  
       (User FOR State IN (State1, State2, State3)  
)
SELECT ItemID, COUNT(Distinct User)
FROM unpvt
GROUP BY ItemID