Forum Discussion

KarlNixon's avatar
KarlNixon
Copper Contributor
Mar 15, 2021
Solved

Simple View

Hi, am new to SQL and am seeking some support in writing a query.

 

I have a table of timesheet records (multiple records per day, per employee) which I am looking to summarise into a view by Work Type.

 

The table has the following fields

 

Date

Employee

Work Type (Ordinary, 1.5x and Leave)

Hours

 

Which I am looking to present as a view with the following fields

 

Date

Employee

Hours Type Ordinary

Hours Type 1.5x

Hours Type Leave

 

Any help with the syntax would be greatly appreciated!

 

 

 

 

 

 

 

 

 

  • kodyaz's avatar
    kodyaz
    Mar 17, 2021

    Hello KarlNixon

     

    You can use https://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx structure for your requirement

    Please refer to tutorial for more samples

     

    Here is the SQL query that will help you

     

    select
  *
from
(
  select
    Date, Employee, WorkType, Hours
  from EmployeeWork
) DataTable
PIVOT
(
  SUM(Hours)
  FOR WorkType
  IN (
    [Ordinary],[Leave],[1.5x]
  )
) PivotTable

     

     

1 Reply

Sort By
  • kodyaz's avatar
    kodyaz
    Copper Contributor
    Mar 17, 2021

    Hello KarlNixon

     

    You can use https://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx structure for your requirement

    Please refer to tutorial for more samples

     

    Here is the SQL query that will help you

     

    select
  *
from
(
  select
    Date, Employee, WorkType, Hours
  from EmployeeWork
) DataTable
PIVOT
(
  SUM(Hours)
  FOR WorkType
  IN (
    [Ordinary],[Leave],[1.5x]
  )
) PivotTable

     

     

Resources