在SQL查询中显示串联的值 [英] Show concatenated values in SQL Query

问题描述

我想将我的第一个SQL查询与id_pr值在下面显示的表类型联接.ID_PR值被重复.我想显示来自ident_st列的所有值，并用逗号分隔以表示相同的id_pr和rodz_st值.例如:显示rodz_st ='DZE'和id_pr = 13的所有ident_st值.

I want to join my first sql query with table Types showed below by id_pr value.ID_PR values are repeated. I want to show all values from ident_st column concatenated with comma separator for same id_pr and rodz_st values. For example: Show all ident_st values for rodz_st='DZE' and id_pr=13.

第一个查询:

SELECT NAME, NO FROM ORDERS o
LEFT JOIN TYPES t ON t.ID_ZM = o.ID_PR

表格订单

  ID        ID_ZM NAME    NO  
---------- ---------- ------- --------
    1        12   Dee     333
    2        13   Rods    111

表类型:

ID    ID_PR  RODZ_ST   IDENT_ST   
---------- ---------- ------- --------
16    12      JEW     646101_1
10    12      JEW     236496_2
11    13      JEW     147301_5
15    13      DZE     259435_1
12    13      OBR     452171_3
13    13      OBR     286432_6
17    12      DZE     618054_1
19    13      DZE     182235_4

我想要如下结果:

NAME    NO      JEW               DZE                  OBR
------- -----  ---------------- ------------------  -----------------
Dee     333   646101_1, 236496_2 618054_1
Rods    111   147301_5           259435_1, 182235_4 452171_3, 286432_6

问题:如何使用级联语句创建sql join以显示结果?

推荐答案

您可以将LISTAGG列表功能与DECODE一起使用:

You may use list LISTAGG function with DECODE :

SELECT NAME, NO, 
       LISTAGG(DECODE(RODZ_ST,'JEW',IDENT_ST,NULL), ',') WITHIN GROUP (ORDER BY t.ID DESC, RODZ_ST) AS JEWS,
       LISTAGG(DECODE(RODZ_ST,'DZE',IDENT_ST,NULL), ',') WITHIN GROUP (ORDER BY t.ID , RODZ_ST) AS DZE,
       LISTAGG(DECODE(RODZ_ST,'OBR',IDENT_ST,NULL), ',') WITHIN GROUP (ORDER BY t.ID , RODZ_ST) AS OBR
  FROM ORDERS o
   LEFT JOIN TYPES t ON t.ID_PR = o.ID_ZM
  GROUP BY NAME, NO;

SQL小提琴演示

这篇关于在SQL查询中显示串联的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！