SOLVED

Simple View

%3CLINGO-SUB%20id%3D%22lingo-sub-2210219%22%20slang%3D%22en-US%22%3ESimple%20View%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2210219%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20am%20new%20to%20SQL%20and%20am%20seeking%20some%20support%20in%20writing%20a%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20of%20timesheet%20records%20(multiple%20records%20per%20day%2C%20per%20employee)%20which%20I%20am%20looking%20to%20summarise%20into%20a%20view%20by%20Work%20Type.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20has%20the%20following%20fields%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDate%3C%2FP%3E%3CP%3EEmployee%3C%2FP%3E%3CP%3EWork%20Type%20(Ordinary%2C%201.5x%20and%20Leave)%3C%2FP%3E%3CP%3EHours%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20I%20am%20looking%20to%20present%20as%20a%20view%20with%20the%20following%20fields%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDate%3C%2FP%3E%3CP%3EEmployee%3C%2FP%3E%3CP%3EHours%20Type%20Ordinary%3C%2FP%3E%3CP%3EHours%20Type%201.5x%3C%2FP%3E%3CP%3EHours%20Type%20Leave%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20with%20the%20syntax%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2216561%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20View%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2216561%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402296%22%20target%3D%22_blank%22%3E%40KarlNixon%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20%3CA%20href%3D%22https%3A%2F%2Fwww.kodyaz.com%2Farticles%2Ft-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ESQL%20Pivot%20query%3C%2FA%3E%20structure%20for%20your%20requirement%3C%2FP%3E%3CP%3EPlease%20refer%20to%20tutorial%20for%20more%20samples%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20SQL%20query%20that%20will%20help%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eselect%0A%20%20*%0Afrom%0A(%0A%20%20select%0A%20%20%20%20Date%2C%20Employee%2C%20WorkType%2C%20Hours%0A%20%20from%20EmployeeWork%0A)%20DataTable%0APIVOT%0A(%0A%20%20SUM(Hours)%0A%20%20FOR%20WorkType%0A%20%20IN%20(%0A%20%20%20%20%5BOrdinary%5D%2C%5BLeave%5D%2C%5B1.5x%5D%0A%20%20)%0A)%20PivotTable%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Visitor

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!

 

 

 

 

 

 

 

 

 

1 Reply
best response confirmed by KarlNixon (Regular Visitor)
Solution

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