SQL query to add a column value as a separate row

Viewed 48

I have a query like -

SELECT
    person_number,
    pay_num,
    reg_hours,
    Hour3,
    hour4,
    Work_sched
FROM 
    (
       -- a SUB QUERY
    )

This is returning data like this:

person_number       pay_num         reg_hours       Hour3  hour3_code       hour4   hour4_code      Work_sched
---------------------------------------------------------------------------------------------------------------
1                   109             10               09     SCK              4          UNP         Shift_a
1                   109                              8      JUR                                     Shift_a

2                   209             8               10      PTD             10         SOL          Shift_b
2                   209                             6.5     REGR                                    Shift_b

I want the reg_hours to be NULL for all cases, and the hour3 column should be added with one more row with the reg_hours value and code 'STR'.

Something like :

person_number       pay_num         reg_hours       Hour3  hour3_code       hour4   hour4_code      Work_sched
1                   109                              09     SCK              4          UNP         Shift_a
1                   109                              8      JUR                                     Shift_a
1                   109                              10     STR                                     Shift_a

2                   209                             10      PTD             10         SOL          Shift_b
2                   209                             6.5     REGR                                    Shift_b
2                   209                             8       STR                                     Shift_b

I wrote this query for it:

SELECT 
    person_number,
    pay_num,
    NULL reg_hours,
    CASE 
        WHEN reg_hours IS NOT NULL 
            THEN 'STR'
            ELSE  HOUR3_CODE
    END AS HOUR3_CODE
    CASE 
        WHEN reg_hours IS NOT NULL 
            THEN reg_hours
            ELSE Hour3
    END AS Hour3,
    hour4,
    Work_sched
FROM 
    (
        -- a SUB QUERY
    )

This is not returning the desired output, and is in fact replacing the existing hour3 and hour3_code instead of adding a line of reg_hours.

How to tweak this ?

1 Answers

I would recommend to implement this with a union query:

  • first half to produce the original rows (but reg_hours set to NULL)

  • second half to add the new rows with the STR code

This may result in a duplicate SUB QUERY, which can be avoided using the WITH statement (like shown here),

Related