时间戳

首页 > 解决方案 > 使用混合数据和添加列进行透视

sql - 使用混合数据和添加列进行透视

问题描述

SQL Server 2014

我有一个包含如下字段的 TaskDetails 表：

JobNo (text)
TaskName (text name of task)
ACTUAL_DATE (A date)
FORECAST_DATE (F date)
TaskType (text code)

每个作业任务一条记录。

我可以让它作为这些列的任务名​​列表

JobNo  Task1A  Task1F     Task2A    Task2F ....  
A123   1/1/20  5/12/19

每个作业一个记录，每个任务的预测和实际作为列

但我想为每个任务代码的 TaskType 添加一列：

JobNo  Task1A  Task1F   Task1T   Task2A    Task2F  Tast2T....  
A123   1/1/20  5/12/19  active   2/1/10    3/1/11  delayed

与上面相同，但添加一个仅包含 TaskType 的列以及任务的 F 和 A 日期的每对列。

这是我正在使用（并且几乎不理解）作业名称和任务日期列的现有代码：

SELECT *
FROM (

SELECT JobNo 
    ,TaskName + 'F' AS TaskCode
    ,FORECAST_DATE AS TaskDate
    FROM Task_Details AS FcstDateQuery
WHERE FORECAST_DATE IS NOT NULL

UNION

SELECT JobNo
    ,TaskName + 'A' AS TaskCode
    ,ACTUAL_DATE AS TaskDate
    FROM Task_Details AS ActDateQuery
WHERE ACTUAL_DATE IS NOT NULL
) 
    AS TaskDateQuery
    PIVOT(MAX(TaskDate) FOR TaskCode IN (
         [Code1F]
        ,[Code1A]
        ,[Code2F]
        ,[Code2A]
        )
        )as Pivot1

无法弄清楚如何添加该列。

样本数据：

任务详情

JobNo, Taskname, Actual_Date,Forecast_Date,Tasktype
A111,  Name1,     1/1/20,     1/1/30,       delayed
A111,  Name2,     2/2/20,     3/3/20,       active
A222,  Name1,     3/3/20,     4/4/20,       cancel
A222,  Name2,     4/4/20,     5/5/20,       pending

电流输出：

JobNo, Name1F, Name1A,  Name2F, Name2A
A111, 1/1/20,  1/1/30,  2/2/20, 3/3/20  
A222, 3/3/20,  4/4/20,  4/4//20, 5/5/20

期望的输出

JobNo, Name1F, Name1A, Name1T,  Name2F, Name2A, Name2T
A111, 1/1/20,  1/1/30, delayed, 2/2/20, 3/3/20, active 
A222, 3/3/20,  4/4/20, cancel,  4/4//20, 5/5/20, pending

任务详细信息表中的每一行都有一个任务，它是 2 个日期和状态信息。我需要输出每个 JobNo 有一行，并且在每一行中，每个任务日期的列（现在正在工作）和任务状态的另一列（最后一部分是我需要帮助的）

编辑：++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++

根据@ilyazakharov 的输入，我注意到答案将日期转换为字符串。那是我真正遇到的原始问题，我无法合并这两种数据类型。如果我将日期转换为 stings，我的原始代码就可以工作。贴在下面。但是所有日期字段的输出都是 varchar，我需要它们是日期的日期。示例代码只有几个任务名称，实际数据有数百个。无需进行所有铸造即可获得输出。或者一些更雄辩的方式。

我还通过做 2 个枢轴让它工作。一个用于我现在的任务，另一个用于 char 数据字段，然后将这两个连接在一起。想过会有更好的方法吗？

CREATE TABLE Task_Details
(
    JobNo             NVARCHAR(100),
    TaskName          NVARCHAR(100),
    ACTUAL_DATE       DATE,
    FORECAST_DATE     DATE,
    TaskType          NVARCHAR(100)
)

INSERT INTO Task_Details
  (
    JobNo,
    TaskName,
    ACTUAL_DATE,
    FORECAST_DATE,
    TaskType
  )
VALUES
('A111','Name1','2020-01-01','2030-01-01','delayed'),
('A111', 'Name2', '2020-02-02', '2020-03-03', 'ACTIVE'),
('A222', 'Name1', '2020-03-03', '2020-04-04', 'cancel'),
('A222', 'Name2', '2020-04-04', '2020-05-05', 'pending')

SELECT 
  JobNo
  ,cast(Name1F as smalldatetime) as Name1F
  ,cast(Name1A as smalldatetime) as Name1A
  ,Name1T
  ,cast(Name2F as smalldatetime) as Name2F
  ,cast(Name2A as smalldatetime) as Name2A
  ,Name2T

FROM (

SELECT JobNo 
    ,TaskName + 'T' AS TaskCode
    ,Tasktype AS TaskDate
    FROM deleteme_taskdet AS FcstDateQuery
WHERE FORECAST_DATE IS NOT NULL

union

SELECT JobNo 
    ,TaskName + 'F' AS TaskCode
    ,CAST(FORECAST_DATE AS NVARCHAR(100)) AS TaskDate

    FROM deleteme_taskdet AS FcstDateQuery
WHERE FORECAST_DATE IS NOT NULL

UNION

SELECT JobNo
    ,TaskName + 'A' AS TaskCode
    ,CAST(ACTUAL_DATE AS NVARCHAR(100)) AS TaskDate
    FROM deleteme_taskdet AS ActDateQuery
WHERE ACTUAL_DATE IS NOT NULL
) 
    AS TaskDateQuery
    PIVOT(MAX(TaskDate) FOR TaskCode IN (
     [Name1F]
    ,[Name1A]
    ,[Name1T]
,[Name2F]
    ,[Name2A]
    ,[Name2T]            )
        )as Pivot1

标签： sqlsql-server

解决方案

您应该首先取消透视表以为 TaskType 和 TaskDate 创建一列，然后旋转这个新列。应该是这样的：

CREATE TABLE Task_Details
(
    JobNo             NVARCHAR(100),
    TaskName          NVARCHAR(100),
    ACTUAL_DATE       DATE,
    FORECAST_DATE     DATE,
    TaskType          NVARCHAR(100)
)

INSERT INTO Task_Details
  (
    JobNo,
    TaskName,
    ACTUAL_DATE,
    FORECAST_DATE,
    TaskType
  )
VALUES
('A111','Name1','2020-01-01','2030-01-01','delayed'),
('A111', 'Name2', '2020-02-02', '2020-03-03', 'ACTIVE'),
('A222', 'Name1', '2020-03-03', '2020-04-04', 'cancel'),
('A222', 'Name2', '2020-04-04', '2020-05-05', 'pending')

SELECT JobNo,
       cast([Name1A_TaskDate] as smalldatetime) AS Name1F,
       cast([Name1F_TaskDate] as smalldatetime) AS Name1A,
       [Name1F_TaskType]  AS Name1T,
       cast([Name2A_TaskDate] as smalldatetime) AS Name2F,
       cast([Name2F_TaskDate] as smalldatetime) AS Name2A,
       [Name2F_TaskType] AS Name2T
FROM   (
           SELECT JobNo,
                  task,
                  TaskCode + '_' + JOB  AS rr
           FROM   (
                      SELECT JobNo,
                             TaskName + 'F' AS TaskCode,
                             CAST(FORECAST_DATE AS NVARCHAR(100)) AS TaskDate,
                             TaskType
                      FROM   Task_Details AS FcstDateQuery
                      WHERE  FORECAST_DATE IS NOT NULL
                      UNION
                      SELECT JobNo,
                             TaskName + 'A' AS TaskCode,
                             CAST(ACTUAL_DATE AS NVARCHAR(100)) AS TaskDate,
                             TaskType
                      FROM   Task_Details AS ActDateQuery
                      WHERE  ACTUAL_DATE IS NOT NULL
                  )                     AS t
                  UNPIVOT(Task FOR JOB IN ([TaskDate], [TaskType])) AS h
       ) AS t 
       PIVOT(
           MAX(task) FOR rr IN ([Name1A_TaskDate], [Name1F_TaskDate], [Name1F_TaskType], [Name2A_TaskDate], 
                               [Name2F_TaskDate], [Name2F_TaskType])
       ) AS pvt

推荐阅读