SQL Server:创建转置表并与现有表联接

Question

我有一组来自表 [MSPWIP] 的数据。MSPWIP]。[事件] 如下所示：

| Createdby | StationName | SerialNumber |
-------------------------------------------------------
|   Jay     |    L1.A1    | 22191321572  |
|   Allan   |    L1.A2    | 22191321572  |
|   Nathan  |    L2.A1    | 22191321579  |
|   Jane    |    L2.A2    | 22191321579  |

我还有其他数据集，我已经在另一个与问题无关的查询中加入

我想创建一个表，将运算符(由 createdby 表示)按 L1 的站点分隔开来。例如，A1 表示 1 号线第 1 站。目前对我来说，Line无关紧要

重组后我的理想数据应该如下所示

| SerialNumber | Operator1 | Operator2 |
----------------------------------------
| 22191321572  |    Jay    |   Allan   |
| 22191321579  |   Nathan  |   Jane    |

我尝试使用此代码连接两个表：

查询#1

Declare @Operator1 Table(
SerialNumber Varchar(255),
Operator1 Varchar(255)
)
Insert Into @Operator1 (Serialnumber, Operator1)
Select 
SerialNumber,
Createdby as Operator1
From [MSPWIP].[MSPWIP].[Event]
where StationName like '%01'
Declare @Operator2 Table(
SerialNumber Varchar(255),
Operator2 Varchar(255)
)
Insert Into @Operator2 (Serialnumber, Operator2)
Select 
SerialNumber,
CreatedBy as Operator2
From [MSPWIP].[MSPWIP].[Event]
where StationName like '%02'
select 
a.SerialNumber,
CreatedBy,
b.Operator2
From @Operator1 a
join @Operator2 b
On a.SerialNumber = b.SerialNumber
Where a.SerialNumber In ('22191321572', '22191321574')

然后我想使用以下代码将其与其他查询连接起来：

查询#2

join @Operator1 i
on a.SerialNumber = i.SerialNumber
join @Operator2 j
on a.SerialNumber = j.SerialNumber

• 请注意，a 是不同的表。

但是，对于 Query#1，它只设法显示标题而不是数据，这也导致 Query#2 也显示标题而没有其他内容。

只是想知道无法将数据插入列的 Query#1 是否有问题？

====

================================================

更新：

使用下面的答案(带有修改)我想出了这样的代码

查询#3

SELECT Distinct*
FROM (
SELECT distinct
SerialNumber,
Case When t.StationName like '%A1' then CreatedBy End Operator1,
Case When t.StationName like '%A2' then CreatedBy End Operator2
--,   Max(CASE WHEN CAST(RIGHT(t.StationName, 1) AS Varchar(255)) = 1 THEN t.CreatedBy END) Operator1 
--,   Max(CASE WHEN CAST(RIGHT(t.StationName, 1) AS Varchar(255)) = 2 THEN t.CreatedBy END) Operator2 
FROM [MSPWIP].[MSPWIP].[Event] t
where t.CreatedDate > '2019-05-30'
Group BY SerialNumber, StationName, Createdby
) d 

然而，我的结果现在变得如此错开：

| SerialNumber | Operator1 | Operator2 |
----------------------------------------
| 22191321572  |    Jay    |   NULL    |
| 22191321572  |    NULL   |   Allan   |
| 22191321579  |   Nathan  |   NULL    |
| 22191321579  |    NULL   |   Jane    |

我在这里做错了什么吗？

Answer 1

您可以通过像这样在一次运行中执行此操作来节省时间：

SELECT *
FROM (
SELECT 
SerialNumber
,   MAX(CASE WHEN RIGHT(t.StationName, 2) = '01' THEN t.Operator END) Operator1 
,   MAX(CASE WHEN RIGHT(t.StationName, 2) = '02' THEN t.Operator END) Operator2 
FROM [MSPWIP].[MSPWIP].[Event] t
GROUP BY SerialNumber
) d 

然后，您只需将其与所需的表连接即可。

P.S ：如果你在StationName中的站部分并不总是一个数字，那么你可以使用SUBSTRING(t.StationName, CHARINDEX('.', t.StationName) + 1, LEN(t.StationName))而不是RIGHT(t.StationName, 2)来获取站部分(在点之后)。