我有一个查询结果，需要把数据整理成特定格式。其中一个新列的数据，可能来自原始数据表中的三列中的任意一列，取决于一个字段的代码。这个代码是表中的另一个字段。见下方图片中的示例。我需要找出CA1_ID、CA2_ID或 CA3_ID中包含值CA的记录。这个值可能出现在它们中的任意一个。如果它们都不包含CA，那么就把这三个字段拼接在一起。你可以看到，在某个示例中，CA值存在于CA1（根据CA1_ID），因此我想要CA1字段中的值。第二个示例中，CA出现在CA2（基于CA2_ID），因此我想要CA2字段中的值。我正在研究如何实现这个。这既可以是一个过程实现，但如果可能的话，最好用单条查询来完成。我现在对哪种实现都可以接受。

到目前为止我进展到这里，但还没有成功。

SELECT        
    WF1_DATA.PROGRAM AS PROGRAM_ID, 
    WF1_DATA.SECTOR, 
    --This is what I was trying, but doesnt work.
    CASE 
        WHEN A.CA_FIELD IS NOT NULL THEN (SELECT A.CA_FIELD FROM WF1_DATA B WHERE A.PROGRAM = B.PROGRAM AND A.SECTOR = B.SECTOR)
        ELSE NULL
    END AS Test,
    --control_account_1 AS CONTROL_ACCOUNT, 
    --control_account_1_desc AS CONTROL_ACCOUNT_DESCRIPTION, 
    WP AS WORK_PACKAGE, 
    DESCRIP AS WORK_PACKAGE_DESCRIPTION, 
    CECODE AS BOE_CATEGORY, 
    CEDESC AS BOE_CATEGORY_DESCRIPTION, 
    ForecastStartDate AS WP_SD, 
    ForecastEndDate AS WP_ED, 
    HOURS, 
    FTE
FROM            
    WF1_DATA
    LEFT OUTER JOIN
     (
        SELECT DISTINCT
            [SECTOR],
            [PROGRAM],
            CASE
                WHEN CA1_ID IN ('WBS') THEN 'CA1'
                WHEN CA2_ID IN ('WBS') THEN 'CA2'
                WHEN CA3_ID IN ('WBS') THEN 'CA3'
                ELSE NULL
            END AS WBS_FIELD,
            CASE
                WHEN CA1_ID IN ('OBS') THEN 'CA1'
                WHEN CA2_ID IN ('OBS') THEN 'CA2'
                WHEN CA3_ID IN ('OBS') THEN 'CA3'
                ELSE NULL
            END AS OBS_FIELD,
            CASE
                WHEN CA1_ID IN ('CA','CONTROL ACCOUNT', 'CONTROL_ACCOUNT') THEN 'CA1'
                WHEN CA2_ID IN ('CA','CONTROL ACCOUNT', 'CONTROL_ACCOUNT') THEN 'CA2'
                WHEN CA3_ID IN ('CA','CONTROL ACCOUNT', 'CONTROL_ACCOUNT') THEN 'CA3'
                ELSE NULL
            END AS CA_FIELD
        FROM 
            WF1_DATA
        ) A ON A.PROGRAM = WF1_DATA.PROGRAM and A.SECTOR = WF1_DATA.SECTOR

结果只是把CA1或 CA2的值放入CONTROL_ACCOUNT字段中，而不是来自CA的值。

数据源WF1_DATA

CREATE TABLE [dbo].[WF1_DATA](
    [SECTOR] [nvarchar](8) NULL,
    [PROGRAM] [nvarchar](22) NULL,
    [CAWPID] [int] NULL,
    [MANAGER] [nvarchar](59) NULL,
    [CA1_ID] [nvarchar](10) NULL,
    [CA1_BD] [nvarchar](22) NULL,
    [CA1] [nvarchar](59) NULL,
    [CA1_DESC] [nvarchar](254) NULL,
    [CA2_ID] [nvarchar](10) NULL,
    [CA2_BD] [nvarchar](22) NULL,
    [CA2] [nvarchar](59) NULL,
    [CA2_DESC] [nvarchar](254) NULL,
    [CA3_ID] [nvarchar](10) NULL,
    [CA3_BD] [nvarchar](22) NULL,
    [CA3] [nvarchar](59) NULL,
    [CA3_DESC] [nvarchar](254) NULL,
    [WP_ID] [nvarchar](20) NULL,
    [WP] [nvarchar](59) NULL,
    [DESCRIP] [nvarchar](254) NULL,
) ON [PRIMARY]
GO

INSERT [dbo].[WF1_DATA] ([SECTOR], [PROGRAM], [CAWPID], [MANAGER], [CA1_ID], [CA1_BD], [CA1], [CA1_DESC], [CA2_ID], [CA2_BD], [CA2], [CA2_DESC], [CA3_ID], [CA3_BD], [CA3], [CA3_DESC], [WP_ID], [WP], [DESCRIP]) VALUES (N'SECTOR1', N'TEST1', -2074554109, N' ', N'CA', N'TEST1_WBS', N'121-ATP', N'Test and Checkout', N'', N'', N'', N'', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR49', N'Production 1100')
GO
INSERT [dbo].[WF1_DATA] ([SECTOR], [PROGRAM], [CAWPID], [MANAGER], [CA1_ID], [CA1_BD], [CA1], [CA1_DESC], [CA2_ID], [CA2_BD], [CA2], [CA2_DESC], [CA3_ID], [CA3_BD], [CA3], [CA3_DESC], [WP_ID], [WP], [DESCRIP]) VALUES (N'SECTOR1', N'TEST1', -2074554109, N' ', N'CA', N'TEST1_WBS', N'121-ATP', N'Test and Checkout', N'', N'', N'', N'', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR49', N'Production 1100')
GO
INSERT [dbo].[WF1_DATA] ([SECTOR], [PROGRAM], [CAWPID], [MANAGER], [CA1_ID], [CA1_BD], [CA1], [CA1_DESC], [CA2_ID], [CA2_BD], [CA2], [CA2_DESC], [CA3_ID], [CA3_BD], [CA3], [CA3_DESC], [WP_ID], [WP], [DESCRIP]) VALUES (N'SECTOR1', N'TEST1', -2074554109, N' ', N'CA', N'TEST1_WBS', N'121-ATP', N'Test and Checkout', N'', N'', N'', N'', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR49', N'Production 1100')
GO
INSERT [dbo].[WF1_DATA] ([SECTOR], [PROGRAM], [CAWPID], [MANAGER], [CA1_ID], [CA1_BD], [CA1], [CA1_DESC], [CA2_ID], [CA2_BD], [CA2], [CA2_DESC], [CA3_ID], [CA3_BD], [CA3], [CA3_DESC], [WP_ID], [WP], [DESCRIP]) VALUES (N'SECTOR1', N'TESTA', 2074554110, N' ', N'', N'', N'', N'', N'CA', N'TESTA_WBS', N'122-ATP', N'Checkout', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR50', N'Production 2100')
GO
INSERT [dbo].[WF1_DATA] ([SECTOR], [PROGRAM], [CAWPID], [MANAGER], [CA1_ID], [CA1_BD], [CA1], [CA1_DESC], [CA2_ID], [CA2_BD], [CA2], [CA2_DESC], [CA3_ID], [CA3_BD], [CA3], [CA3_DESC], [WP_ID], [WP], [DESCRIP]) VALUES (N'SECTOR1', N'TESTA', 2074554110, N' ', N'', N'', N'', N'', N'CA', N'TESTA_WBS', N'122-ATP', N'Checkout', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR50', N'Production 2100')
GO
INSERT [dbo].[WF1_DATA] ([SECTOR], [PROGRAM], [CAWPID], [MANAGER], [CA1_ID], [CA1_BD], [CA1], [CA1_DESC], [CA2_ID], [CA2_BD], [CA2], [CA2_DESC], [CA3_ID], [CA3_BD], [CA3], [CA3_DESC], [WP_ID], [WP], [DESCRIP]) VALUES (N'SECTOR1', N'TESTA', 2074554110, N' ', N'', N'', N'', N'', N'CA', N'TESTA_WBS', N'122-ATP', N'Checkout', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR50', N'Production 2100')
GO

解决方案

根据你给出的定义和示例数据，你可以使用几条 CASE 表达式来实现。没有关于如果没有 CA 值时数据应如何呈现的示例，因此我改用 CONCAT_WS，在各值之间用逗号连接来组合它们。不过，这确实假设你的零长度字符串实际上是 NULL 值。如果不是，当它们是零长度字符串时，你需要把每个值包裹在一个 NULLIF 中，以便用 NULL 来处理它们。

SELECT WD.SECTOR AS Sector,
       WD.PROGRAM AS ProgramID,
       CASE 'CA' WHEN WD.CA1_ID THEN WD.CA1
                 WHEN WD.CA2_ID THEN WD.CA2
                 WHEN WD.CA3_ID THEN WD.CA3
                 ELSE CONCAT_WS(',',WD.CA1,WD.CA2,WD.CA3)
       END AS ControlAccount,
       CASE 'CA' WHEN WD.CA1_ID THEN WD.CA1_DESC
                 WHEN WD.CA2_ID THEN WD.CA2_DESC
                 WHEN WD.CA3_ID THEN WD.CA3_DESC
                 ELSE CONCAT_WS(',',WD.CA1_DESC,WD.CA2_DESC,WD.CA3_DESC)
       END AS ControlAccountDescription,
       WD.WP AS WorkPackage,
       WD.DESCRIP AS WorkPackageDescription
FROM dbo.WF1_DATA WD;

坦率地说，我仍然建议你重新考虑设计。你缺少为每一行分配一个标识符，表是非规范化的；你确实应该有两张表并定义一对多的关系。此外，如果你存储零长度字符串，我建议改用 NULL 值。

db<>fiddle