sql - SQL 将单独的时间戳合并为每个序列号 1 行

Question

我有 tableA 列 SerialNumber(integer)、LineNumber(integer)、SectionNumber(integer) 和 TimeComplete(Datetime)。此表跟踪序列号为 x 的产品何时在任何给定生产线上的任何给定站点完成。我现在正在尝试构建一个查询，该查询将显示任何给定序列号的任何给定站点何时完成，但问题是我收到每个已完成部分的行项目。我希望输出是带有序列号的单行，然后是每个部分的时间戳。

Current output
SerialNumber   Station1    Station2   Station3   Station4
123            TimeStamp   null       null       null
123            null        timestamp  null       null
123            null        null       timestamp  null
123            null        null       null       timestamp

Desired output
SerialNumber  Station1    Station2   Station3   Station4
123           TimeStamp   Timestamp  Timestamp  Timestamp

当前sql查询

SELECT
distinct
tableA.Serial,
case when tableA.Section = 4 then tableA.TimeComplete
end as 'Station1',
case when tableA.Section = 5 then tableA.TimeComplete
end as 'Station2',
case when tableA.Section = 2 then tableA.TimeComplete
end as 'Station3',
case when tableA.Section = 6 then tableA.TimeComplete
end as 'Station4',
FROM tableA

标签： sqlsql-servertsqltimestamppivot

Answer 1

您可以进行聚合：

SELECT Serial
       MAX(case when Section = 4 then TimeComplete end) as 'Station1',
       MAX(case when Section = 5 then TimeComplete end) as 'Station2',
       MAX(case when Section = 2 then TimeComplete end) as 'Station3',
       MAX(case when Section = 6 then TimeComplete end) as 'Station4'
FROM tableA AS a
GROUP BY Serial;