Turning User one Status Columns into multiple Flag Columns and remove Duplicate rows in SQL Server-CodePudding

I have a table like sample below with user ID and Status of each user.

User_ID Status
123     Healthy
123     Rich
123     Studied
456     Sick
456     Rich
678     Sick
678     Rich
678     Studied
910     Rich
910     Studied

I need to turn the status column into three flags columns in SQL Server and remove any duplicate User ID rows to be like the table below, so if a user has no record in previous table their flag will be "No". Any Advice?

User_ID Healthy Rich Studied
123     Yes     Yes     Yes
456     Yes     Yes     No
678     Yes     Yes     Yes
910     No      Yes     Yes

CodePudding user response：

select
user_id,
max(case when Status='Healthy' then 'Yes' else 'No' end) as healthy,
max(case when Status='Rich' then 'Yes' else 'No' end) as rich,
max(case when Status='Studied' then 'Yes' else 'No' end) as studied
from input
group by user_id;