Geek asks and answers

Row List Order in Oracle SQL without LISTAGG

I work with two objects: Itemand Attribute, which look something like this:

Item
----
itemId

Attribute
---------
attributeId
name

An Itemhas Attributes, as indicated in the association table:

ItemAttribute
--------------
itemId
attributeId

When this data reaches the client, it will be displayed with a line behind Item, and each line will have a list Attributeby name. For instance:

Item  Attributes
----  ----------
1     A, B, C
2     A, C
3     A, B

The user will be able to sort by column Attributes, so we need to sort the data as follows:

Item  Attributes
----  ----------
3     A, B
1     A, B, C
2     A, C

Currently we are getting one row of data per row ItemAttribute. Primarily:

  SELECT Item.itemId,
         Attribute.name
    FROM Item
    JOIN ItemAttribute
      ON ItemAttribute.itemId = Item.itemId
    JOIN Attribute
      ON Attribute.attributeId = ItemAttribute.attributeId
ORDER BY Item.itemId;

Which gives the result, for example:

itemId  name
------  ----
1       A
1       B
1       C
2       A
2       C
3       A
3       B

ORDER BY . , , , , Attribute , , . , , Oracle , - - :

itemId  name
------  ----
3       A
3       B
1       A
1       B
1       C
2       A
2       C

Oracle LISTAGG ; Attribute.name , , 4000 , .

, Oracle SQL (11gR2)?

+5
3

:

1) 4000

?

- 4000 , CLOB. , Tom Kyte thread (, , CLOB).

( 10g):

SQL> CREATE TYPE tab_varchar2 AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION concat_array(p tab_varchar2) RETURN CLOB IS
  2     l_result CLOB;
  3  BEGIN
  4     FOR cc IN (SELECT column_value FROM TABLE(p) ORDER BY column_value) LOOP
  5        l_result := l_result ||' '|| cc.column_value;
  6     END LOOP;
  7     return l_result;
  8  END;
  9  /

Function created.

SQL> SELECT item,
  2         concat_array(CAST (collect(attribute) AS tab_varchar2)) attributes
  3    FROM data
  4   GROUP BY item;

ITEM ATTRIBUTES
1    a b c
2    a c
3    a b

2)

, Oracle: .

  • clob ORA-00932: : - CLOB.
  • , , ( VARCHAR2, ), ORA-06502: PL/SQL: :

4000 :

SQL> SELECT * FROM (
  2     SELECT item,
  3            concat_array(CAST (collect(attribute) AS tab_varchar2)) attributes
  4       FROM data
  5      GROUP BY item
  6  ) order by dbms_lob.substr(attributes, 4000, 1);

ITEM ATTRIBUTES
3    a b
1    a b c
2    a c
+4

, ( CLOB, ).

, 10g , XMLAgg:

with ItemAttribute  as (
 select 'name'||level name
        ,mod(level,3) itemid
   from dual
  connect by level < 2000
)
,ItemAttributeGrouped as (
 select xmlagg(xmlparse(content name||' ' wellformed) order by name).getclobval() attributes
       ,itemid
   from ItemAttribute
  group by itemid
 )
select itemid
      ,attributes
      ,dbms_lob.substr(attributes,4000,1) sortkey
  from ItemAttributeGrouped
order by dbms_lob.substr(attributes,4000,1)
;
+1

, ( , , , ), , , - lead :

SELECT itemId, name
  FROM (
    SELECT itemId, name, min(dr) over (partition by itemId) as dr
      FROM (
        SELECT itemId, name,
            dense_rank() over (order by name, name1, name2, name3, name4) as dr
          FROM (
            SELECT Item.itemId,
                     Attribute.name,
                     LEAD(Attribute.name, 1)
                         OVER (PARTITION BY Item.itemId
                             ORDER BY Attribute.name) AS name1,
                     LEAD(Attribute.name, 2)
                         OVER (PARTITION BY Item.itemId
                             ORDER BY Attribute.name) AS name2,
                     LEAD(Attribute.name, 3)
                         OVER (PARTITION BY Item.itemId
                             ORDER BY Attribute.name) AS name3,
                     LEAD(Attribute.name, 4)
                         OVER (PARTITION BY Item.itemId
                             ORDER BY Attribute.name) AS name4
                FROM Item
                JOIN ItemAttribute
                  ON ItemAttribute.itemId = Item.itemId
                JOIN Attribute
                  ON Attribute.attributeId = ItemAttribute.attributeId
               )
          )
      )
ORDER BY dr, name;

, , , lead ( , , , , , !), , . :

    ITEMID NAME       NAME1      NAME2      NAME3      NAME4
---------- ---------- ---------- ---------- ---------- ----------
         1 A          B          C
         1 B          C
         1 C
         2 A          C
         2 C
         3 A          B
         3 B

dense_rank , itemID name, :

    ITEMID NAME               DR
---------- ---------- ----------
         1 A                   1
         1 B                   4
         1 C                   6
         2 A                   3
         2 C                   6
         3 A                   2
         3 B                   5

dr itemID, min, itemID=1 min(dr) = 1, itemId=2 3, itemId=3 2. ( , min(dense_rank(...)), () ).

, :

    ITEMID NAME
---------- ----------
         1 A
         1 B
         1 C
         3 A
         3 B
         2 A
         2 C
0

More articles:


All Articles