Column to row transform in SQL Server

 Q :  In an organization,  staff tasks had been assigned through CRM and task status (New, Complete, Pending) submitted to CRM back. Now Admin want to see the every staff task status New, Compare and pending order with staff name

Table Structure

 Solution 

SQL server Pivot function enables you to transform column to row order table presentation. You will need to do it in three steps.

1. Generate your SQL output
2. Save it in a temporary variable
3. combined temporary variable output with pivot 

Main Query : 

SELECT        WebsiteUser.Name, case when StaffTask.Status=0 then 'New' when  StaffTask.Status=1 then 'complete' when  StaffTask.Status=3 then 'Pending' end as status,

COUNT(StaffTask.Status) AS result

FROM            StaffTask INNER JOIN

                         WebsiteUser ON StaffTask.CreatedBy = WebsiteUser.ID

GROUP BY WebsiteUser.Name, StaffTask.Status

ORDER BY WebsiteUser.Name

Output

Holding output in a temporary variable 

SELECT * FROM  

(

SELECT        WebsiteUser.Name, case when StaffTask.Status=0 then 'New' when  StaffTask.Status=1 then 'Complete' when  StaffTask.Status=3 then 'Pending' end as status,

COUNT(StaffTask.Status) AS result

FROM            StaffTask INNER JOIN

                         WebsiteUser ON StaffTask.CreatedBy = WebsiteUser.ID

                                          where WebsiteUser.status=1

GROUP BY WebsiteUser.Name, StaffTask.Status,WebsiteUser.status

 

) t

 

Combine with Pivot

SELECT * FROM  

(

SELECT        WebsiteUser.Name, case when StaffTask.Status=0 then 'New' when  StaffTask.Status=1 then 'Complete' when  StaffTask.Status=3 then 'Pending' end as status,

COUNT(StaffTask.Status) AS result

FROM            StaffTask INNER JOIN

                         WebsiteUser ON StaffTask.CreatedBy = WebsiteUser.ID

                                          where WebsiteUser.status=1

GROUP BY WebsiteUser.Name, StaffTask.Status,WebsiteUser.status

 ) t

PIVOT(

    sum(result)

    FOR status IN (

        [New],

               [Complete],

        [Pending]

      

)) AS pivot_table;        

 

 Next Topic