从SQL表插入和转换数据

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)

Status_Time(秒)(他们如何处于特定状态,以秒为单位)

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

我想将此数据放在另一个表中,该表将Status_ID分组为列。此表包含如下列:

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.

因此,Status_ID 2和3可能会在休假,Status_ID 1午餐等分组。

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.

我曾想过在一个while循环中嵌套一个Case,遍历每一行以插入到我的另一个表中。但是,我无法将这些数据从行中的Status_ID插入到现在按其分组的列中。

2 个解决方案

#1

There's no need for a WHILE loop.

不需要WHILE循环。

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.

此外,将status_time保留在表中是一个错误(除非它是非持久的计算列)。您实际上是将相同的数据存储在数据库中的两个位置,这最终会导致不一致。将此数据推送到另一个表中也是如此,其中按状态类型划分时间。不要创建新表来保存数据,使用查询在需要时获取数据。

#2

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

这种类型的查询(将值从行转换为列)被命名为pivot query(SQL Server)或交叉表(Access)。

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

有两种类型的数据透视查询(一般来说):

With a fixed number of columns.

具有固定数量的列。

With a dynamic number of columns.

具有动态列数。

SQL Server support both types but:

SQL Server支持这两种类型但是:

Database Engine (query language: T-SQL) support directly only pivot queries with a fixed number of columns(1) and indirectly (2)

数据库引擎(查询语言:T-SQL)仅支持具有固定列数(1)和间接(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.

Analysis Services(查询语言:MDX)直接支持两种类型(1和2)。此外,您可以使用OPENQUERY / OPENROWSET函数或使用具有四部分名称的链接服务器从T-SQL查询(MDX)Analysis Service数据源。

T-SQL (only) solutions:

T-SQL(仅限)解决方案:

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

对于第一种类型(1),从SQL Server 2005开始,您可以使用PIVOT运算符:

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

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:

对于动态数量的列(2),T-SQL仅提供间接解决方案:动态查询。首先,您必须从Status_ID中找到所有不同的值,然后下一步是构建最终查询:

    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;

#1