Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Объединение записей по группе в одну строку  [new]
ora-newbie
Member

Откуда:
Сообщений: 47
Есть таблица вида.

idclient   product
-----------------
100       Product1
100       Product2
200       Product1
300       Product1
300       Product2
300       Product3

Нужно получить результат:

idclient product
-----------------------------------
100      Product1, Product2
200      Product1
300      Product1, Product2, Product3

Подскажите как реализовать такое на Oracle Sql/PL Sql?
12 сен 07, 16:20    [4657660]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
xymbo
Member

Откуда: Донской --> Москва
Сообщений: 2560
Очень часто эта тема всплывает
ТОП №14
12 сен 07, 16:23    [4657689]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
SELECT  incident,
        ltrim(sys_connect_by_path(product,', '),', ') product
  FROM  (
         SELECT  incident,
                 product,
                 row_number() over(partition by incident order by product) rn
        )
  WHERE rn = 1
  CONNECT BY incident = PRIOR incident
      AND rn = PRIOR rn + 1
/

SY.

Сообщение было отредактировано: 12 сен 07, 16:28
12 сен 07, 16:26    [4657723]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
xymbo
Member

Откуда: Донской --> Москва
Сообщений: 2560
with t as (
  select 100 idclient, 'Product1' product from dual
  union all
  select 100 idclient, 'Product2' product from dual
  union all
  select 200 idclient, 'Product1' product from dual
  union all
  select 300 idclient, 'Product1' product from dual
  union all
  select 300 idclient, 'Product2' product from dual
  union all
  select 300 idclient, 'Product3' product from dual)
select idclient, 
  max(decode(rn, 1, product, null)) 
    || ', ' || max(decode(rn, 2, product, null))
    || ', ' || max(decode(rn, 3, product, null))
from (select idclient, product, row_number() over (partition by idclient order by product) rn from t)
group by idclient
12 сен 07, 16:30    [4657771]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
SY
SELECT  incident,
        ltrim(sys_connect_by_path(product,', '),', ') product
  FROM  (
         SELECT  incident,
                 product,
                 row_number() over(partition by incident order by product) rn
        )
  WHERE rn = 1
  CONNECT BY incident = PRIOR incident
      AND rn = PRIOR rn + 1
/

SY.


Nonsense . I need another cup of coffee to wake up:

SELECT  incident,
        ltrim(sys_connect_by_path(product,', '),', ') product
  FROM  (
         SELECT  incident,
                 product,
                 row_number() over(partition by incident order by product) rn,
                 count(*) over(partition by incident) cnt,
        )
  WHERE rn = cnt
  START WITH rn = 1
  CONNECT BY incident = PRIOR incident
      AND rn = PRIOR rn + 1
/

SY.

Сообщение было отредактировано: 12 сен 07, 16:38
12 сен 07, 16:37    [4657841]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
xymbo
Member

Откуда: Донской --> Москва
Сообщений: 2560
SY
SY
SELECT  incident,
        ltrim(sys_connect_by_path(product,', '),', ') product
  FROM  (
         SELECT  incident,
                 product,
                 row_number() over(partition by incident order by product) rn
        )
  WHERE rn = 1
  CONNECT BY incident = PRIOR incident
      AND rn = PRIOR rn + 1
/

SY.


Nonsense . I need another cup of coffee to wake up:

SELECT  incident,
        ltrim(sys_connect_by_path(product,', '),', ') product
  FROM  (
         SELECT  incident,
                 product,
                 row_number() over(partition by incident order by product) rn,
                 count(*) over(partition by incident) cnt,
        )
  WHERE rn = cnt
  START WITH rn = 1
  CONNECT BY incident = PRIOR incident
      AND rn = PRIOR rn + 1
/

SY.

SELECT  idclient,
        ltrim(sys_connect_by_path(product,', '),', ') product
  FROM  (
         SELECT  idclient,
                 product,
                 row_number() over(partition by idclient order by product) rn,
                 count(*) over(partition by idclient) cnt
         FROM t
        )
  WHERE rn = cnt
  START WITH rn = 1
  CONNECT BY idclient = PRIOR idclient
      AND rn = PRIOR rn + 1
12 сен 07, 16:39    [4657863]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
ora-newbie
Member

Откуда:
Сообщений: 47
trim(sys_connect_by_path(product,', ',', ')

Invalid number of arguments.
12 сен 07, 16:39    [4657868]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
ora-newbie
trim(sys_connect_by_path(product,', ',', ')

Invalid number of arguments.


LTRIM

SY.
12 сен 07, 16:41    [4657886]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
ora-newbie
Member

Откуда:
Сообщений: 47
xymbo
with t as (
  select 100 idclient, 'Product1' product from dual
  union all
  select 100 idclient, 'Product2' product from dual
  union all
  select 200 idclient, 'Product1' product from dual
  union all
  select 300 idclient, 'Product1' product from dual
  union all
  select 300 idclient, 'Product2' product from dual
  union all
  select 300 idclient, 'Product3' product from dual)
select idclient, 
  max(decode(rn, 1, product, null)) 
    || ', ' || max(decode(rn, 2, product, null))
    || ', ' || max(decode(rn, 3, product, null))
from (select idclient, product, row_number() over (partition by idclient order by product) rn from t)
group by idclient


Если кол-во продуктов неизвестно?
12 сен 07, 16:42    [4657894]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
ora-newbie
Member

Откуда:
Сообщений: 47
SY
ora-newbie
trim(sys_connect_by_path(product,', ',', ')

Invalid number of arguments.


LTRIM

SY.


Тоже самое.
12 сен 07, 16:44    [4657919]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
ora-newbie
Тоже самое.


SQL> column product format a50
SQL> with t as (
  2    select 100 idclient, 'Product1' product from dual
  3    union all
  4    select 100 idclient, 'Product2' product from dual
  5    union all
  6    select 200 idclient, 'Product1' product from dual
  7    union all
  8    select 300 idclient, 'Product1' product from dual
  9    union all
 10    select 300 idclient, 'Product2' product from dual
 11    union all
 12    select 300 idclient, 'Product3' product from dual)
 13  SELECT  idclient,
 14          ltrim(sys_connect_by_path(product,', '),', ') product
 15    FROM  (
 16           SELECT  idclient,
 17                   product,
 18                   row_number() over(partition by idclient order by product) rn,
 19                   count(*) over(partition by idclient) cnt
 20           FROM t
 21          )
 22    WHERE rn = cnt
 23    START WITH rn = 1
 24    CONNECT BY idclient = PRIOR idclient
 25        AND rn = PRIOR rn + 1
 26  /

  IDCLIENT PRODUCT
---------- --------------------------------------------------
       100 Product1, Product2
       200 Product1
       300 Product1, Product2, Product3

SQL> 

SY.
12 сен 07, 16:48    [4657953]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
xymbo
Member

Откуда: Донской --> Москва
Сообщений: 2560
ora-newbie
xymbo
with t as (
  select 100 idclient, 'Product1' product from dual
  union all
  select 100 idclient, 'Product2' product from dual
  union all
  select 200 idclient, 'Product1' product from dual
  union all
  select 300 idclient, 'Product1' product from dual
  union all
  select 300 idclient, 'Product2' product from dual
  union all
  select 300 idclient, 'Product3' product from dual)
select idclient, 
  max(decode(rn, 1, product, null)) 
    || ', ' || max(decode(rn, 2, product, null))
    || ', ' || max(decode(rn, 3, product, null))
from (select idclient, product, row_number() over (partition by idclient order by product) rn from t)
group by idclient


Если кол-во продуктов неизвестно?

А тогда надо создавать функцию, и ИМХО, это будет самым правильным.
12 сен 07, 16:49    [4657980]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
ora-newbie
Member

Откуда:
Сообщений: 47
SY
ora-newbie
Тоже самое.


SQL> column product format a50
SQL> with t as (
  2    select 100 idclient, 'Product1' product from dual
  3    union all
  4    select 100 idclient, 'Product2' product from dual
  5    union all
  6    select 200 idclient, 'Product1' product from dual
  7    union all
  8    select 300 idclient, 'Product1' product from dual
  9    union all
 10    select 300 idclient, 'Product2' product from dual
 11    union all
 12    select 300 idclient, 'Product3' product from dual)
 13  SELECT  idclient,
 14          ltrim(sys_connect_by_path(product,', '),', ') product
 15    FROM  (
 16           SELECT  idclient,
 17                   product,
 18                   row_number() over(partition by idclient order by product) rn,
 19                   count(*) over(partition by idclient) cnt
 20           FROM t
 21          )
 22    WHERE rn = cnt
 23    START WITH rn = 1
 24    CONNECT BY idclient = PRIOR idclient
 25        AND rn = PRIOR rn + 1
 26  /

  IDCLIENT PRODUCT
---------- --------------------------------------------------
       100 Product1, Product2
       200 Product1
       300 Product1, Product2, Product3

SQL> 

SY.


Вариант класс. То, что нужно. Скажите, а как сделать, чтобы продукты добавлялись в порядке возрастания дат, например

idclient   dat        product
----------------------------
100       01.05.2007  Product1
100       01.01.2007  Product2
200       01.01.2007  Product1
300       01.01.2007  Product1
300       22.06.2007  Product2
300       10.01.2007  Product3

Чтобы получалось

idclient product
-----------------------------------
100      Product2, Product2
200      Product1
300      Product1, Product3, Product2
13 сен 07, 15:38    [4663739]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
Elic
Member

Откуда:
Сообщений: 29977
ora-newbie
Вариант класс. То, что нужно. Скажите, а как сделать, чтобы продукты добавлялись в порядке возрастания дат
Извилин совсем нет, чтобы напрячь чуток?
Вместо разжёвывания подсказка: переведи указанное слово.
13 сен 07, 15:53    [4663897]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
ora-newbie
Member

Откуда:
Сообщений: 47
Разобрался.

ROW_NUMBER() OVER(PARTITION BY t.idclient ORDER BY t.dat) num,
13 сен 07, 15:54    [4663906]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
ora-newbie
Member

Откуда:
Сообщений: 47
Elic
ora-newbie
Вариант класс. То, что нужно. Скажите, а как сделать, чтобы продукты добавлялись в порядке возрастания дат
Извилин совсем нет, чтобы напрячь чуток?
Вместо разжёвывания подсказка: переведи указанное слово.


Не стоит быть столь категоричным, я только учусь. Для меня все в новинку.
13 сен 07, 15:56    [4663916]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
ora-newbie
Member

Откуда:
Сообщений: 47
SY
ora-newbie
Тоже самое.


SQL> column product format a50
SQL> with t as (
  2    select 100 idclient, 'Product1' product from dual
  3    union all
  4    select 100 idclient, 'Product2' product from dual
  5    union all
  6    select 200 idclient, 'Product1' product from dual
  7    union all
  8    select 300 idclient, 'Product1' product from dual
  9    union all
 10    select 300 idclient, 'Product2' product from dual
 11    union all
 12    select 300 idclient, 'Product3' product from dual)
 13  SELECT  idclient,
 14          ltrim(sys_connect_by_path(product,', '),', ') product
 15    FROM  (
 16           SELECT  idclient,
 17                   product,
 18                   row_number() over(partition by idclient order by product) rn,
 19                   count(*) over(partition by idclient) cnt
 20           FROM t
 21          )
 22    WHERE rn = cnt
 23    START WITH rn = 1
 24    CONNECT BY idclient = PRIOR idclient
 25        AND rn = PRIOR rn + 1
 26  /
  IDCLIENT PRODUCT
---------- --------------------------------------------------
       100 Product1, Product2
       200 Product1
       300 Product1, Product2, Product3

SQL> 

SY.


Скажите, а с чем может быть связано следующее явление. В таблице 25 млн. записей. Используя следующий запрос все обрабатывается нормально, но часть idclient выпадает по непонятной причине. Т.е. часть записей просто не обрабатывается и они не попадают в результирующую выборку.
14 сен 07, 09:53    [4667047]     Ответить | Цитировать Сообщить модератору
 Re: Объединение записей по группе в одну строку  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
ora-newbie
Скажите, а с чем может быть связано следующее явление. В таблице 25 млн. записей. Используя следующий запрос все обрабатывается нормально, но часть idclient выпадает по непонятной причине. Т.е. часть записей просто не обрабатывается и они не попадают в результирующую выборку.


Based on В таблице 25 млн. записей I suspect
product1, ..., product n
for some idclents exceeds 4000 bytes. If so, SYS_CONNECT_BY_PATH is not the right instrument. Unfortunately, and I can't understand why, SYS_CONNECT_BY_PATH does not support CLOB. Anyway, if the above is true, you can use my Hierarchy package and modify to support CLOB:

CREATE OR REPLACE
  PACKAGE Hierarchy
    IS
        TYPE BranchTableVarchar2Type IS TABLE OF VARCHAR2(4000)
          INDEX BY BINARY_INTEGER;
        BranchTableVarchar2 BranchTableVarchar2Type;
        TYPE BranchTableClobType IS TABLE OF CLOB
          INDEX BY BINARY_INTEGER;
        BranchTableClob BranchTableClobType;
        FUNCTION Branch(
                        p_Level          IN NUMBER,
                        p_Value          IN VARCHAR2,
                        p_Delimiter      IN VARCHAR2 DEFAULT CHR(0)
                       )
          RETURN VARCHAR2;
        PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
        FUNCTION Branch(
                        p_Level          IN NUMBER,
                        p_Value          IN CLOB,
                        p_Delimiter      IN VARCHAR2 DEFAULT CHR(0)
                       )
          RETURN CLOB;
        PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
END Hierarchy;
/
CREATE OR REPLACE
  PACKAGE BODY Hierarchy
    IS
        ReturnValueVarchar2 VARCHAR2(4000);
        ReturnValueClob     CLOB;
    FUNCTION Branch(
                    p_Level        IN NUMBER,
                    p_Value        IN VARCHAR2,
                    p_Delimiter    IN VARCHAR2 DEFAULT CHR(0)
                   )
      RETURN VARCHAR2
      IS
      BEGIN
          BranchTableVarchar2(p_Level) := p_Value;
          ReturnValueVarchar2          := p_Value;
          FOR I IN REVERSE 1..p_Level - 1 LOOP
            ReturnValueVarchar2 := BranchTableVarchar2(I)|| p_Delimiter || ReturnValueVarchar2;
          END LOOP;
          RETURN ReturnValueVarchar2;
    END Branch;
    FUNCTION Branch(
                    p_Level        IN NUMBER,
                    p_Value        IN CLOB,
                    p_Delimiter    IN VARCHAR2 DEFAULT CHR(0)
                   )
      RETURN CLOB
      IS
      BEGIN
          BranchTableClob(p_Level) := p_Value;
          ReturnValueClob          := p_Value;
          FOR I IN REVERSE 1..p_Level - 1 LOOP
            ReturnValueClob := BranchTableClob(I)|| p_Delimiter || ReturnValueClob;
          END LOOP;
          RETURN ReturnValueClob;
    END Branch;
END Hierarchy;
/

and then change

ltrim(sys_connect_by_path(product,', '),', ') product

to

Hierarchy.Branch(level,to_clob(product),', ') product

SY.
14 сен 07, 18:02    [4671446]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить