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! http://msdn.microsoft.com/en-us/library/ms177410.aspx — Mike Christensen, Jan 26 '12 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, Jan 26 '12 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., Jan 26 '12 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., Jan 26 '12 at 21:07

score 3 · Accepted Answer · answered Jan 26 '12 at 19:55

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

score 1 · Answer 2 · edited May 23 '17 at 12:20

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 '17 at 12:20

Community

1
1

answered Jan 26 '12 at 19:58

Cade Roux

88,164
40
182
265

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! – D.R. Jan 26 '12 at 21:04

score 1 · Answer 3 · answered Jan 26 '12 at 20:00

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 '12 at 20:00

JeffO

7,957
3
44
53

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! – D.R. Jan 26 '12 at 21:02

Dynamic SQL columns

3 Answers3