根据条件将表中的数据拆分为2列

Question

必须根据select子句中的conditions.All将表中的一列拆分为2列。

我的表有一列detail_code。有两个细节代码，.I希望每个细节代码在我的select子句中都作为单独的列。

我尝试了subquery，但它抛出了多行的subquery返回。

select id,(select detail_code from detc where type_ind ='C')as Detail_code1,
        (select detail_code from detc where type_ind ='P')as Detail_code2,
        (Select sum(amount) from amount_tbl where detail_code in (select detail_code from detc where type_ind ='C')and term_code='2019')as amt_detail_code1 
        from id_table;

我的输出应该是

Id  Detail_code1  Detail_code2  sum(amt_Detail_Code1)  sum(amt_Detail_code2)
1          C                P                  15                  45
2          C                P                 785                  74

我的输入

ID     Detail_cd   ind  amt
317002687   CA20    C   3
317002687   CA21    C   60
317002687   CA23    C   18.75
317002687   CA25    C   179.64
317002687   CA26    C   136.5
317002687   CA27    C   8.25
317002687   CA28    C   4
317002687   CA2B    C   8
317002687   CA2H    C   6.75
317002687   CA2I    C   237
317002687   CA2J    C   65.4

Answer 1

此查询将生成您所描述的输出：

SELECT id, 'C' AS detail_code1, 'P' AS detail_code2,
       SUM(CASE WHEN ind = 'C' THEN amt END) AS sum_detail_code1,
       SUM(CASE WHEN ind = 'P' THEN amt END) AS sum_detail_code2
FROM name_of_your_input_table
GROUP BY id;

或者，由于您知道两个详细信息代码是什么，因此可以将它们作为列名，并将每列的值作为该详细信息代码的平均值。要做到这一点，一种方法是旋转。下面是Oracle的语法：

SELECT *
FROM (SELECT id, ind AS detail_code, amt
      FROM name_of_your_input_table)
PIVOT (AVG(amt)
       FOR detail_code IN ('C', 'P'));