Inserting and transforming data from SQL table

I 开发者_如何学Gohave 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.

---

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.

---

This type of query (that transpose values from rows into columns) is named pivot query (SQL Server) or crosstab (Access).

There is two types of pivot queries (generally speaking):

1. With a fixed number of columns.
2. With a dynamic number of columns.

SQL Server support both types but:

• Database Engine (query language: T-SQL) support directly only pivot queries with a fixed number of columns(1) and indirectly (2)
• Analysis Services (query language: MDX) support directly both types (1 & 2). Also, you can query(MDX) Analysis Service data sources from T-SQL using OPENQUERY/OPENROWSET functions or using a linked server with four-part names.

T-SQL (only) solutions:

For the first type (1), starting with SQL Server 2005 you can use the PIVOT operator:

SELECT pvt.*
FROM
(
SELECT Date, Id, Status_ID, Status_Time
FROM Table
) src
PIVOT ( SUM(src.Status_Time) FOR src.Status_ID IN ([1], [2], [3]) ) pvt

or

SELECT pvt.Date, pvt.Id, pvt.[1] AS Lunch, pvt.[2] AS [Break], pvt.[3] Vacation
FROM
(
SELECT Date, Id, Status_ID, Status_Time
FROM Table
) src
PIVOT ( SUM(src.Status_Time) FOR src.Status_ID IN ([1], [2], [3]) ) pvt

For a dynamic number of columns (2), T-SQL offers only an indirect solution: dynamic queries. First, you must find all distinct values from Status_ID and the next move is to build the final query:

    DECLARE @SQLStatement NVARCHAR(4000)
            ,@PivotValues NVARCHAR(4000);
    SET @PivotValues = '';

    SELECT  @PivotValues = @PivotValues + ',' + QUOTENAME(src.Status_ID)
    FROM
    (
            SELECT DISTINCT Status_ID
            FROM Table
    ) src;
    SET @PivotValues = SUBSTRING(@PivotValues,2,4000);

    SELECT  @SQLStatement = 
    'SELECT pvt.*
    FROM
    (
    SELECT Date, Id, Status_ID, Status_Time
    FROM Table
    ) src
    PIVOT ( SUM(src.Status_Time) FOR src.Status_ID IN ('+@PivotValues+') ) pvt';

    EXECUTE sp_executesql @SQLStatement;