SQL DB2 -对于具有相同列的不同值，使用Y或N的Case语句

Question

我有一个复杂的SQL，它在多列中返回值，我需要将输出返回到单行。

select distinct TableA.ColumnA, d.name,
      (CASE WHEN rel1.REL_TYPE = 'FG' THEN 'Y' ELSE 'N' END) AS  FG
      (CASE WHEN rel2.REL_TYPE = 'F1' THEN 'Y' ELSE 'N' END) AS  F1,
      (CASE WHEN rel3.REL_TYPE = 'F2' THEN 'Y' ELSE 'N' END) AS  F2,
      (CASE WHEN rel4.REL_TYPE = 'F3' THEN 'Y' ELSE 'N' END) AS  F3 
 from TableA d
      inner join TableB p on TableA.ColumnA = p.ColumnB
      inner join TableC rel1 on  TableA.ColumnA = rel1.ColumnC  
        inner join TableC rel2 on  TableA.ColumnA = rel2.ColumnC 
        inner join TableC rel3 on  TableA.ColumnA = rel3.ColumnC 
        inner join TableC rel14 on  TableA.ColumnA = rel4.ColumnC        
where d.language_id   = -1  and  TableA.ColumnA = 53635
  and p.language_id   = -1
order by 1
 with ur;

并且输出有多行，我需要输出到单行

53635   Fitness Fleece, Jacket  N   N   N   N
53635   Fitness Fleece, Jacket  N   N   N   Y
53635   Fitness Fleece, Jacket  Y   N   N   N
53635   Fitness Fleece, Jacket  Y   N   N   Y
53635   Fitness Fleece, Jacket  N   Y   N   N
53635   Fitness Fleece, Jacket  N   Y   N   Y
53635   Fitness Fleece, Jacket  Y   Y   N   N
53635   Fitness Fleece, Jacket  Y   Y   N   Y
53635   Fitness Fleece, Jacket  N   N   Y   N
53635   Fitness Fleece, Jacket  N   N   Y   Y
53635   Fitness Fleece, Jacket  Y   N   Y   N
53635   Fitness Fleece, Jacket  Y   N   Y   Y
53635   Fitness Fleece, Jacket  N   Y   Y   N
53635   Fitness Fleece, Jacket  N   Y   Y   Y
53635   Fitness Fleece, Jacket  Y   Y   Y   N
53635   Fitness Fleece, Jacket  Y   Y   Y   Y

我正在寻找单行输出，相应地显示FG，F1，F2和F3的rel_type的Y或N(rel_type将由许多rel_types组成，如MN，OG等)

53635   Fitness Fleece, Jacket  Y   Y   Y   N

Answer 1

好了，我想我终于明白这些要求了。您可以将MAX与CASE和GROUP BY一起使用--不需要单独连接表：

select columna, 
  name,
  max(case when rel_type = 'FG' then 'Y' else 'N' end) 'FG',
  max(case when rel_type = 'F1' then 'Y' else 'N' end) 'F1',
  max(case when rel_type = 'F2' then 'Y' else 'N' end) 'F2',
  max(case when rel_type = 'F3' then 'Y' else 'N' end) 'F3'
from tablea
group by columna, name

SQL Fiddle Demo

显然，将您的WHERE条件添加回来，但这应该是您正在寻找的。