Transforming table of categorical data in SQL Server-CodePudding

I have two columns Status and Verified Status that I want to transform to columns for each of their unique values for counts. How exactly can I do this in SQL Server?

Input

Year   Status     Verified_Status
---------------------------------
2021   Accepted   Verified
2021   Accepted   Verified
2021   Rejected   Unverified
2021   Accepted   Unverified
2021   Accepted   Unverified 
2020   Rejected   Unverified
2020   Accepted   Unverified
2020   Accepted   Unverified

Expected output

Year  Accepted Rejected Verified Unverified
-------------------------------------------
2021  4        1        2        3
2020  2        1        0        3

CodePudding user response：

As Larnu alluded to... a simple conditional aggregation should do the trick

Select Year
      ,Accepted   = sum( case when [Status]='Accepted' then 1 else 0 end )
      ,Rejected   = sum( case when [Status]='Rejected' then 1 else 0 end )
      ,Verified   = sum( case when [Verified_Status]='Verified' then 1 else 0 end )
      ,Unverified = sum( case when [Verified_Status]='Unverified' then 1 else 0 end )
 From  YourTable
 Group By Year
 Order By Year Desc  -- << Optional

CodePudding user response：

Select 
        Count(Case When Status = 'Accepted' then 1 end) Accepted,
        Count(Case When Status = 'Rejected' then 1 end) Rejected,
        Count(Case When Status = 'Verified' then 1 end) Verified,
        Count(Case When Status = 'Unverified' then 1 end) Unverified
    from T