Forum Discussion

KarlNixon's avatar
KarlNixon
Copper Contributor
Mar 15, 2021

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 SQL Pivot query 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

     

     

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

    Hello KarlNixon

     

    You can use SQL Pivot query 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