SQL合并1对多关联多行数据为单行输出的实现方案咨询

尝试编写SQL实现同一账户关联的多只基金/ETF合并为单行输出，未得到预期结果，相关表结构和测试数据如下：

CREATE TABLE [dbo].[Acct](
    [Account_ID] [nchar](10) NULL,
    [Account_Type] [nchar](10) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[ETF](
    [ETF_ID] [nchar](10) NULL,
    [ETF_Name] [nchar](10) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Fund](
    [Fund_ID] [nchar](10) NULL,
    [Fund_Name] [nchar](10) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Rel](
    [Rel_Account_ID] [nchar](10) NULL,
    [Rel_Fund_ETF_ID] [nchar](10) NULL,
    [Row_id] [nchar](10) NULL
) ON [PRIMARY]

INSERT [dbo].[Acct] ([Account_ID], [Account_Type]) VALUES (N'ZA001T12  ', N'INDV      ')
INSERT [dbo].[Acct] ([Account_ID], [Account_Type]) VALUES (N'ZA001T09  ', N'INDV      ')

INSERT [dbo].[ETF] ([ETF_ID], [ETF_Name]) VALUES (N'FUETAR    ', N'ETFAR     ')

INSERT [dbo].[Fund] ([Fund_ID], [Fund_Name]) VALUES (N'FUESPY    ', N'FNSPY     ')
INSERT [dbo].[Fund] ([Fund_ID], [Fund_Name]) VALUES (N'FUFSPY    ', N'FUFSPY    ')

INSERT [dbo].[Rel] ([Rel_Account_ID], [Rel_Fund_ETF_ID], [Row_id]) VALUES (N'ZA001T12  ', N'FUESPY    ', N'1         ')
INSERT [dbo].[Rel] ([Rel_Account_ID], [Rel_Fund_ETF_ID], [Row_id]) VALUES (N'ZA001T12  ', N'FUETAR    ', N'2         ')
INSERT [dbo].[Rel] ([Rel_Account_ID], [Rel_Fund_ETF_ID], [Row_id]) VALUES (N'ZA001T09  ', N'FUESPX    ', N'3         ')
INSERT [dbo].[Rel] ([Rel_Account_ID], [Rel_Fund_ETF_ID], [Row_id]) VALUES (N'ZA001T12  ', N'FUFSPY    ', N'4         ')

此前的SQL方案仅适配账户与基金/ETF1:1关联的场景，当关系表中同一账户对应多条关联记录时，输出会丢失额外数据。移除WHERE条件后输出为多行重复数据，添加全匹配条件后无结果返回，实际测试输出如下：

同一账户所有关联的基金、ETF分别展示在不同字段，单账户仅输出一行，示例如下：

可行性判断 #

要求“按关联数量动态新增字段”的展示方案无法直接落地，SQL为强结构化查询语言，输出列数固定，无法根据数据行数动态增减字段。仅可在两种场景下实现类似效果：1. 提前确定单账户最多持有基金/ETF数量，提前预留对应字段；2. 改为将多值合并为单个字符串字段输出。

可落地方案 #

方案1：固定列行转列（适用于单账户最多持有产品数确定的场景） #

先给关联记录按产品类型打标排序，再用聚合函数合并为单行，可直接输出你要求的预期格式：

WITH RelWithType AS (
    SELECT 
        TRIM(R.Rel_Account_ID) AS Rel_Account_ID,
        TRIM(R.Rel_Fund_ETF_ID) AS Rel_Fund_ETF_ID,
        CASE WHEN F.Fund_ID IS NOT NULL THEN 'FUND' 
             WHEN E.ETF_ID IS NOT NULL THEN 'ETF' 
             ELSE 'UNKNOWN' END AS ProductType,
        ROW_NUMBER() OVER (PARTITION BY R.Rel_Account_ID, 
                           CASE WHEN F.Fund_ID IS NOT NULL THEN 'FUND' WHEN E.ETF_ID IS NOT NULL THEN 'ETF' END 
                           ORDER BY R.Row_id) AS rn
    FROM Rel R
    LEFT JOIN Fund F ON R.Rel_Fund_ETF_ID = F.Fund_ID
    LEFT JOIN ETF E ON R.Rel_Fund_ETF_ID = E.ETF_ID
)
SELECT 
    TRIM(A.Account_ID) AS Acc_ID,
    TRIM(A.Account_Type) AS Acc_Typ,
    RW.Rel_Account_ID AS Rl_Acc_ID,
    MAX(CASE WHEN ProductType = 'FUND' AND rn =1 THEN RW.Rel_Fund_ETF_ID END) AS Fnd_ID_1,
    MAX(CASE WHEN ProductType = 'ETF' AND rn =1 THEN RW.Rel_Fund_ETF_ID END) AS ETF_ID,
    MAX(CASE WHEN ProductType = 'FUND' AND rn =2 THEN RW.Rel_Fund_ETF_ID END) AS Fnd_ID_2
FROM Acct A
LEFT JOIN RelWithType RW ON A.Account_ID = RW.Rel_Account_ID
GROUP BY A.Account_ID, A.Account_Type, RW.Rel_Account_ID

如需支持更多基金，新增对应rn=N的CASE字段即可。

方案2：字符串聚合（适用于单账户持有产品数不确定的场景，兼容性更强） #

直接将同类型产品合并为单个逗号分隔的字段输出，无需提前预留字段：

SELECT 
    TRIM(A.Account_ID) AS Acc_ID,
    TRIM(A.Account_Type) AS Acc_Typ,
    TRIM(R.Rel_Account_ID) AS Rl_Acc_ID,
    STRING_AGG(CASE WHEN F.Fund_ID IS NOT NULL THEN TRIM(R.Rel_Fund_ETF_ID) END, ',') AS All_Fund_IDs,
    STRING_AGG(CASE WHEN E.ETF_ID IS NOT NULL THEN TRIM(R.Rel_Fund_ETF_ID) END, ',') AS All_ETF_IDs
FROM Acct A
LEFT JOIN Rel R ON A.Account_ID = R.Rel_Account_ID
LEFT JOIN Fund F ON R.Rel_Fund_ETF_ID = F.Fund_ID
LEFT JOIN ETF E ON R.Rel_Fund_ETF_ID = E.ETF_ID
GROUP BY A.Account_ID, A.Account_Type, R.Rel_Account_ID

SQL Server 2016及以下版本不支持STRING_AGG，可替换为FOR XML PATH方式实现字符串聚合。

内容的提问来源于stack exchange，提问作者Sathya Kumar