我有一个选择查询,它返回我数据如下:
---------------------------------------
PartCode | ParentCode | Flag |
---------------------------------------
ABC | XYZ | null |
---------------------------------------
PQR | XYZ | Y |
---------------------------------------
现在我想根据标志转置它,如下所示:
-标志为 Y 的 PartCode 应标记为 criticalPart,其他标记为 SecondaryPart。
-总会有两行我想应用此条件。
---------------------------------------------
ParentCode | CriticalPart | SecondaryPart |
---------------------------------------------
XYZ | PQR | ABC |
---------------------------------------------
使用以下代码来实现结果。
select ParentCode
, Max(case when flag = 'Y' then PartCode end) as CriticalPart
, Max(case when flag is null then PartCode end) as SecondaryPart
from table
group by ParentCode
SQL 小提琴链接
我会在父代码上使用两个子查询和一个连接
SELECT critical.ParentCode,
critical.PartCode as CriticalPart,
secondary.PartCode as SecondaryPart
FROM (
SELECT ParentCode, PartCode
FROM Table
WHERE Flag IS NOT NULL
) critical
INNER JOIN (
SELECT ParentCode, PartCode
FROM Table
WHERE Flag IS NULL
) secondary ON critical.ParentCode = secondary.ParentCode