应该根据情况搜索整个表并在一行中提供数据 [英] Should search whole table and give data in one row according to the condition

问题描述

我有一个表，值如下:

AC_NO  INV_NO   CC      FT
--------------------------------
1      A        PTN     INVOICE
1      A        PTN     BDE
2      B        ABC     INVOICE
2      B        PTN     INVOICE
2      B        PTN     BDE

SELECT
ac_no,
CASE  
WHEN FT like '%INVOICE%' THEN 'AVAILABLE' else 'NOTAVAILABLE'
END AS INVOICE ,
CASE  
WHEN FT like '%BDE%' THEN 'AVAILABLE' else 'NOTAVAILABLE'
END AS BDE
FROM Account_info where CC='PTN';

Output 
------------------------------------
1   A   Available      Notavailable
1   A   Notavailable   Available
2   B   Available      Notavailable
2   B   Notavailable   Available

但是我需要采用以下格式:

but I need them in the following format:

AC_NO   INVOICE       BDE
--------------------------------
1       AVAILABLE     AVAILABLE
2       AVAILABLE     AVAILABLE

推荐答案

与COALESCE或NVL

SQL小提琴

查询1 :

  SELECT ac_no,
         COALESCE (MAX (CASE WHEN FT =  'INVOICE' THEN 'AVAILABLE' END),
                   'NOTAVAILABLE')
            AS INVOICE,
         COALESCE (MAX (CASE WHEN FT = 'BDE' THEN 'AVAILABLE' END),
                   'NOTAVAILABLE')
            AS BDE
    FROM Account_info
   WHERE CC = 'PTN'
GROUP BY ac_no

结果 :

Results:

| AC_NO |   INVOICE |       BDE |
|-------|-----------|-----------|
|     1 | AVAILABLE | AVAILABLE |
|     2 | AVAILABLE | AVAILABLE |

这篇关于应该根据情况搜索整个表并在一行中提供数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！