sql server - Inserting and transforming data from SQL table

Question

I have a question which has been bugging me for a couple of days now. I have a table with:

• Date
• ID
• Status_ID
• Start_Time
• End_Time
• Status_Time(seconds) (How ling they were in a certain status, in seconds)

I want to put this data in another table, that has the Status_ID grouped up as columns. This table has columns like this:

• Date
• ID
• Lunch (in seconds)
• Break(in seconds)
• Vacation, (in seconds) etc.

So, Status_ID 2 and 3 might be grouped under vacation, Status_ID 1 lunch, etc.

I have thought of doing a Case nested in a while loop, to go through every row to insert into my other table. However, I cannot wrap my head around inserting this data from Status_ID in rows, to columns that they are now grouped by.

Answer 1

There's no need for a WHILE loop.

SELECT
    date,
    id,
    SUM(CASE WHEN status_id = 1 THEN status_time ELSE 0 END) AS lunch,
    SUM(CASE WHEN status_id = 2 THEN status_time ELSE 0 END) AS break,
    SUM(CASE WHEN status_id = 3 THEN status_time ELSE 0 END) AS vacation
FROM
    My_Table
GROUP BY
    date,
    id

Also, keeping the status_time in the table is a mistake (unless it's a non-persistent, calculated column). You are effectively storing the same data in two places in the database, which is going to end up resulting in inconsistencies. The same goes for pushing this data into another table with times broken out by status type. Don't create a new table to hold the data, use the query to get the data when you need it.