SQL行转列需求：按审批层级拆分多审批人至列，保留4行数据

• 需求：按审批层级将审批人姓名拆分到不同列展示，每个成本中心对应多个审批人，针对成本中心AA1需输出4行数据（因成本中心存在多行关联数据，无法使用MAX/MIN等聚合函数）
• 当前问题：现有SQL查询结果逻辑正确，但需优化输出：仅保留AA1的4行数据，同时去除行内的空值（审批人数不足的层级列可保留空）

原SQL语句 #

select rv.dim_value cost_centre,
(CASE when L1.role_id  like 'W-APP-L1-'+rv.dim_value Then L1.description else null end) AS L1_Approver,
(CASE when L1.role_id  like '%L2%' Then L1.description else null end) AS L2_Approver,
(CASE when L1.role_id  like '%L3%' Then L1.description else null end) AS L3_Approver,
(CASE when L1.role_id  like '%L4%' Then L1.description else null end) AS L4_Approver,
(CASE when L1.role_id  like '%L5%' Then L1.description else null end) AS L5_Approver,
(CASE when L1.role_id  like '%L6%' Then L1.description else null end) AS L6_Approver
from agldimvalue rv
INNER JOIN aagviuserdetail L1 ON rv.dim_value=SUBSTRING(L1.role_id,10,3) and L1.role_id like 'W-APP-%' and CURRENT_TIMESTAMP between L1.date_from and L1.date_to and L1.client='BK' and L1.role_id!=''
INNER JOIN aaguser usr ON usr.user_id=L1.user_id and usr.status='N'
where
rv.client='BK' and rv.attribute_id='C1' and rv.status='N' and rv.dim_value='AA1'
order by rv.dim_value ;

优化方案 #

1. 去除行内空值（替换NULL为空字符串） #

将CASE语句中的else null改为else ''，原本的NULL会显示为空字符串，视觉上消除空值占位：

select rv.dim_value cost_centre,
(CASE when L1.role_id like 'W-APP-L1-' + rv.dim_value Then L1.description else '' end) AS L1_Approver,
(CASE when L1.role_id like '%L2%' Then L1.description else '' end) AS L2_Approver,
(CASE when L1.role_id like '%L3%' Then L1.description else '' end) AS L3_Approver,
(CASE when L1.role_id like '%L4%' Then L1.description else '' end) AS L4_Approver,
(CASE when L1.role_id like '%L5%' Then L1.description else '' end) AS L5_Approver,
(CASE when L1.role_id like '%L6%' Then L1.description else '' end) AS L6_Approver
from agldimvalue rv
INNER JOIN aagviuserdetail L1 ON rv.dim_value=SUBSTRING(L1.role_id,10,3) 
    and L1.role_id like 'W-APP-%' 
    and CURRENT_TIMESTAMP between L1.date_from and L1.date_to 
    and L1.client='BK' 
    and L1.role_id!=''
INNER JOIN aaguser usr ON usr.user_id=L1.user_id and usr.status='N'
where rv.client='BK' 
    and rv.attribute_id='C1' 
    and rv.status='N' 
    and rv.dim_value='AA1'
order by rv.dim_value 

2. 强制保留4行数据 #

如果当前查询可能返回多于4行（比如AA1有超过4个审批人），可在语句末尾添加分页限制：

• MySQL/PostgreSQL：加LIMIT 4
• SQL Server：将select改为select top 4
• Oracle：在where后加and ROWNUM <=4

3. 移除全空的层级列（可选） #

如果某些层级（如L5、L6）在AA1下无审批人，导致对应列全为空，可通过动态SQL自动移除这些列。以SQL Server为例：

DECLARE @cols NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(col), ', ')
FROM (
    SELECT 'L1_Approver' col WHERE EXISTS(SELECT 1 FROM aagviuserdetail L1 WHERE L1.role_id like 'W-APP-L1-AA1' and CURRENT_TIMESTAMP between L1.date_from and L1.date_to and L1.client='BK')
    UNION ALL SELECT 'L2_Approver' WHERE EXISTS(SELECT 1 FROM aagviuserdetail L1 WHERE L1.role_id like '%L2%' and SUBSTRING(L1.role_id,10,3)='AA1' and CURRENT_TIMESTAMP between L1.date_from and L1.date_to and L1.client='BK')
    UNION ALL SELECT 'L3_Approver' WHERE EXISTS(SELECT 1 FROM aagviuserdetail L1 WHERE L1.role_id like '%L3%' and SUBSTRING(L1.role_id,10,3)='AA1' and CURRENT_TIMESTAMP between L1.date_from and L1.date_to and L1.client='BK')
    UNION ALL SELECT 'L4_Approver' WHERE EXISTS(SELECT 1 FROM aagviuserdetail L1 WHERE L1.role_id like '%L4%' and SUBSTRING(L1.role_id,10,3)='AA1' and CURRENT_TIMESTAMP between L1.date_from and L1.date_to and L1.client='BK')
    UNION ALL SELECT 'L5_Approver' WHERE EXISTS(SELECT 1 FROM aagviuserdetail L1 WHERE L1.role_id like '%L5%' and SUBSTRING(L1.role_id,10,3)='AA1' and CURRENT_TIMESTAMP between L1.date_from and L1.date_to and L1.client='BK')
    UNION ALL SELECT 'L6_Approver' WHERE EXISTS(SELECT 1 FROM aagviuserdetail L1 WHERE L1.role_id like '%L6%' and SUBSTRING(L1.role_id,10,3)='AA1' and CURRENT_TIMESTAMP between L1.date_from and L1.date_to and L1.client='BK')
) t;

DECLARE @sql NVARCHAR(MAX) = N'
select rv.dim_value cost_centre, ' + @cols + N'
from agldimvalue rv
INNER JOIN aagviuserdetail L1 ON rv.dim_value=SUBSTRING(L1.role_id,10,3) 
    and L1.role_id like ''W-APP-%'' 
    and CURRENT_TIMESTAMP between L1.date_from and L1.date_to 
    and L1.client=''BK'' 
    and L1.role_id!=''''
INNER JOIN aaguser usr ON usr.user_id=L1.user_id and usr.status=''N''
where rv.client=''BK'' 
    and rv.attribute_id=''C1'' 
    and rv.status=''N'' 
    and rv.dim_value=''AA1''
order by rv.dim_value 
top 4;';

EXEC sp_executesql @sql;

内容的提问来源于stack exchange，提问作者Michelle Boyle