Dynamic SQL columns

Question

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!

Sounds like you're looking for the PIVOT operator! msdn.microsoft.com/en-us/library/ms177410.aspx — Mike Christensen
– Mike Christensen, Commented Jan 26, 2012 at 19:52
Check out PIVOT. You will need to name the columns though (i.e. you can't dynamically have the query adjust to changing data in the Tag column). — Yuck
– Yuck, Commented Jan 26, 2012 at 19:52
@Yuck: I have strict tag values so hard coding that is not a problem! I'll definitely look into it! — D.R.
– D.R., Commented Jan 26, 2012 at 20:07
As always, thank you everyone for the quick responses that were perfectly what I needed. I had thought about cases, but am horrible with the syntax because I'm so used to SWITCH/CASE statements in Java and C++. Thanks again! — D.R.
– D.R., Commented Jan 26, 2012 at 21:07

D'Arcy Rittich · Accepted Answer · 2012-01-26 19:55:51Z

3

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

answered Jan 26, 2012 at 19:55

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:20:01Z

1

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

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jan 26, 2012 at 19:58

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

1 Comment

D.R. Over a year ago

I appreciate the answer and PIVOT tables are definitely an option. As this was being used in a report that does not allow me to use PIVOT sql statements, I've decided to use RedFilter's answer. Thanks for your suggestion!

JeffO · Accepted Answer · 2012-01-26 20:00:22Z

1

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.

answered Jan 26, 2012 at 20:00

JeffO

8,1233 gold badges46 silver badges55 bronze badges

1 Comment

D.R. Over a year ago

That is ok. I am accepting RedFilter's answer over yours simply because it was 5 mins earlier and his actually fits a bit better (tag is just blank, not NULL). I appreciate the answer though!

Collectives™ on Stack Overflow

Dynamic SQL columns

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related