Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 произвольное число столбцов в конструкции "Pivot" (11g)  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
т.е. чтобы в:

select ... from t pivot( SUM( .... ) for nu in ('X','Y','Z')

cтояли не напрямую 'X','Y','Z' а был бы какой то SELECT ... from Alfabet where ...

Cудя по доке, вроде должно проканывать, но у меня почему то ругается на синтаксис (если видит подзапрос в IN)


____________________________________________________________
сейчас работаю на: Oracle 11.1.0.7.0, 64bit, standard edition, SLES-10. APEX 3.2
8 янв 10, 14:25    [8157040]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
P.S. желательно чтоб без динам.sql ясно-дело.
____________________________________________________________
сейчас работаю на: Oracle 11.1.0.7.0, 64bit, standard edition, SLES-10. APEX 3.2[/quot]
8 янв 10, 17:09    [8157535]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10105
Because PIVOT is just a fancy "bell and whistle" around same static:

SUM(
    CASE
      WHEN col1 = 'X' THEN col2
    END
   ) SUM_X,
SUM(
    CASE
      WHEN col1 = 'Y' THEN col2
    END
   ) SUM_Y,

The only dynamic PIVOT supported in 11g is XML. For example:

select  *
  from  (
         select  job,
                 deptno,
                 sal
           from  emp
        ) pivot xml(sum(sal) for job in (select distinct job from emp))
/

SY.
8 янв 10, 18:30    [8157846]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
SY


The only dynamic PIVOT supported in 11g is XML. For example:

select  *
  from  (
         select  job,
                 deptno,
                 sal
           from  emp
        ) pivot xml(sum(sal) for job in (select distinct job from emp))
/

SY.


cпасибо, только непонятно - как потом перевести результат в читабельный формат?
8 янв 10, 19:51    [8158127]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10105
ORAngutang
cпасибо, только непонятно - как потом перевести результат в читабельный формат?


Something like:

with t as (
           select * from (
                          select  job,
                                  deptno,
                                  sal
                            from  emp
                        ) pivot xml(sum(sal) for job in (select distinct job from emp))
          )
select  deptno,
        job,
        total_salary
  from  t,
        xmltable(
                 'for $i in /PivotSet
                  return $i/item'
                  passing t.job_xml
                  columns job varchar2(20) path '/item/column[1]',
                          total_salary number path '/item/column[2]'
                 )
/

    DEPTNO JOB                             TOTAL_SALARY
---------- ------------------------------ -------------
        10 ANALYST
        10 CLERK                                   1300
        10 MANAGER                                 2450
        10 PRESIDENT                               5000
        10 SALESMAN
        20 ANALYST                                 6000
        20 CLERK                                   1900
        20 MANAGER                                 2975
        20 PRESIDENT
        20 SALESMAN
        30 ANALYST

    DEPTNO JOB                             TOTAL_SALARY
---------- ------------------------------ -------------
        30 CLERK                                    950
        30 MANAGER                                 2850
        30 PRESIDENT
        30 SALESMAN                                5600

15 rows selected.

SQL> 

Or:

with t as (
           select * from (
                          select  job,
                                  deptno,
                                  sal
                            from  emp
                        ) pivot xml(sum(sal) for job in (select distinct job from emp))
          )
select  deptno,
        extractvalue(column_value,'/item/column[1]') job,
        extractvalue(column_value,'/item/column[2]') total_salary
  from  t,
        xmltable(
                 'for $i in /PivotSet
                  return $i/item'
                  passing t.job_xml
                 )
/

    DEPTNO JOB                            TOTAL_SALARY
---------- ------------------------------ ------------
        10 ANALYST
        10 CLERK                          1300
        10 MANAGER                        2450
        10 PRESIDENT                      5000
        10 SALESMAN
        20 ANALYST                        6000
        20 CLERK                          1900
        20 MANAGER                        2975
        20 PRESIDENT
        20 SALESMAN
        30 ANALYST

    DEPTNO JOB                            TOTAL_SALARY
---------- ------------------------------ ------------
        30 CLERK                          950
        30 MANAGER                        2850
        30 PRESIDENT
        30 SALESMAN                       5600

15 rows selected.

SQL> 

SY.

Сообщение было отредактировано: 9 янв 10, 05:51
9 янв 10, 05:46    [8159364]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10105
ORAngutang
cпасибо, только непонятно - как потом перевести результат в читабельный формат?


And if you want читабельный формат in a "pivot" format you still need to know number of columns:

with t as (
           select * from (
                          select  job,
                                  deptno,
                                  sal
                            from  emp
                        ) pivot xml(sum(sal) for job in (select distinct job from emp))
          )
select  deptno,
        extractvalue(job_xml,'/PivotSet/item[1]/column[2]') job1,
        extractvalue(job_xml,'/PivotSet/item[2]/column[2]') job2,
        extractvalue(job_xml,'/PivotSet/item[3]/column[2]') job3,
        extractvalue(job_xml,'/PivotSet/item[4]/column[2]') job4,
        extractvalue(job_xml,'/PivotSet/item[5]/column[2]') job5
  from  t
/

    DEPTNO JOB1       JOB2       JOB3       JOB4       JOB5
---------- ---------- ---------- ---------- ---------- ----------
        10            1300       2450       5000
        20 6000       1900       2975
        30            950        2850                  5600

SQL> 

SY.
9 янв 10, 15:20    [8159903]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
SY
you still need to know number of columns:


ИМЕННО этого и хочется избежать!!!
11 янв 10, 13:28    [8165096]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
ORAngutang
SY
you still need to know number of columns:


ИМЕННО этого и хочется избежать!!!


Не выйдет. :)
11 янв 10, 13:34    [8165137]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
suPPLer
ORAngutang
SY
you still need to know number of columns:


ИМЕННО этого и хочется избежать!!!


Не выйдет. :)


будут другие мнения? :-)
11 янв 10, 15:41    [8166145]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
ORAngutang,

можно XML-результат обрабатывать уже в клиенте, динамически рисуя грид. На чём у Вас клиентское ПО написано, и как в нём такое делать - Вам видней.
11 янв 10, 15:54    [8166225]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
suPPLer
ORAngutang,

можно XML-результат обрабатывать уже в клиенте, динамически рисуя грид. На чём у Вас клиентское ПО написано, и как в нём такое делать - Вам видней.


в данном конкретном случае результат пойдёт в CSV-файл и будет открываться скорее всего в
линуксовском Open-Office. :-))))
11 янв 10, 17:04    [8166932]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
ORAngutang,

XSLT - знакомая аббревиатура? :)
11 янв 10, 17:27    [8167092]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
ORAngutang
Member

Откуда:
Сообщений: 1755
suPPLer
ORAngutang,

XSLT - знакомая аббревиатура? :)


честно говоря в последние минуты кратко познакомился )))))
А что есть, есть шанс через это дело решить проблему = "заранее не зная число столбцов представить результат в Pivot виде в файле CSV"

Эх, надоели эти дебри - сделаю-ка я всё-же лучше PL/SQL процедурку c получением желаемого результата через Dynamic-SQL!!! :-)
11 янв 10, 17:40    [8167182]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
VBR_
Guest

with t as (
select xmltype(cursor(
select to_char(sysdate,'dd.mm.yyyy'),level lvl, dbms_random.random() rnd  from dual connect by level<100
)
) xml from dual
)

select xmltype.transform(xml,xmltype(
'<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
 <xsl:output method="text"/>
 <xsl:template match="/">         
    <xsl:for-each select="ROWSET/*">
       <xsl:for-each select="*">
       <xsl:if test="not(position() = last())">
        <xsl:value-of select="concat(text(),'';'')"/>
      </xsl:if>
       <xsl:if test="position() = last()">
        <xsl:value-of select="text()"/>
      </xsl:if>
   </xsl:for-each>;
</xsl:for-each>
 </xsl:template>
</xsl:stylesheet>')).getclobval() from  t

12 янв 10, 13:42    [8170897]     Ответить | Цитировать Сообщить модератору
 Re: произвольное число столбцов в конструкции "Pivot" (11g)  [new]
VBR_
Guest
Лучше так (с переменной)

with t as (
select xmltype(cursor(
select to_char(sysdate,'dd.mm.yyyy'),level lvl, dbms_random.random() rnd  from dual connect by level<100
)
) xml from dual
)

select xmltype.transform(xml,xmltype(
'<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:variable name="chr_10" select="'';&'||'#10;''"/>
 <xsl:output method="text"/>
 <xsl:template match="/">         
    <xsl:for-each select="ROWSET/*">
       <xsl:for-each select="*">
       <xsl:if test="not(position() = last())">
        <xsl:value-of select="concat(text(),'';'')"/>
      </xsl:if>
       <xsl:if test="position() = last()">
        <xsl:value-of select="concat(text(),$chr_10)"/>
      </xsl:if>
   </xsl:for-each>
</xsl:for-each>
 </xsl:template>
</xsl:stylesheet>')).getclobval() from  t
12 янв 10, 14:21    [8171205]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить