CODEWITHSUNDEEP
sqlt-sql
D.R.
D.R.

Reputation: 1219

Dynamic SQL columns

I know this is probably an easy problem that I'm totally overthinking so here goes:

I have a LaborTransaction table with the following columns: (Table 1)

    Laborcode |   Hours   | OTHours | Tag
     JSMITH   |    2.0    |   0.0   | VACATION 
     JSMITH   |    4.0    |   3.0   | PERSONAL
     JSMITH   |    3.0    |   0.0   | VACATION
     JSMITH   |    5.0    |   1.0   | 
     JSMITH   |    7.0    |   4.0   |

I need to generate the following table: (Table 2) 

Laborcode | Regular | Vacation | Personal | OT
 JSMITH   |  12.0   |   5.0    |   4.0    | 8.0

Basically, I just need to sum up all the labor transactions and check the tag.
- If Tag(T1) is blank, then Hours(T1) gets summed up in Regular(T2)
- If Tag(T1) is 'VACATION', then Hours(T1) gets summed up in Vacation(T2)
- If Tag(T1) is 'PERSONAL', then Hours(T1) get summed up in Personal(T2) - OTHours(T1) is summed up across ALL entries into OT(T2)

In my first attempt, I was joining the table on itself but was getting duplicated results. Regular, Vacation, Personal, and OT were adding the same labor transaction multiple times. I am using MS SQL to create the second table. Any and all help is greatly appreciated! Thanks!

Upvotes: 0

Views: 239

Answers (3)

JeffO
JeffO

Reputation: 8043

Select
  LT.Laborcode
  , Sum(case when NullIf(LT.Tag, '') Is Null then Hours end) as Regular
  , Sum(case when LT.Tag = 'VACATIION' then Hours else end) as Vacation
  , Sum(case when LT.Tag = 'Personal' then Hours else end) as Personal
  , Sum(LT.OTHours) as OverTime
from LaborTransaction as LT
Group by LT.Laborcode

This isn't totally dynamic. If you create different Tag values, new columns won't be created. You would have to create code for a new column.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89651

You can do this with a manual pivot using CASE or you can use the PIVOT feature of SQL Server 2005 and up.

If the tags are going to potentially be changing, you can use dynamic SQL to generate the columns:

SQL Server 2005 Pivot on Unknown Number of Columns

Pivot Table and Concatenate Columns

PIVOT in sql 2005

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

select Laborcode,
    sum(case when Tag = '' then Hours end) as Regular,
    sum(case when Tag = 'VACATION' then Hours end) as Vacation,
    sum(case when Tag = 'PERSONAL' then Hours end) as Personal,
    sum(OTHours) as Overtime
from LaborTransaction 
group by Laborcode

Upvotes: 3

Related Questions