Блог

Прежде всего, я хотел бы ПОБЛАГОДАРИТЬ ВАС за то, что зашли и потратили свое драгоценное время на изучение моей проблемы.

У меня есть 2 разные таблицы в базе данных Oracle.

Первая таблица содержит метаданные о столбцах, присутствующих в другой таблице. Думайте о первой таблице (COL_TAB) как о пользовательской версии ALL_TAB_COLS, которая поставляется по умолчанию с Oracle.

 COL_TAB
----------------------------------------------
| TABLE_NAME | COL_NAME   | COL_DESC         | 
----------------------------------------------
| TABLE1     | TAB1_COL_2 | TABLE 1 COLUMN 2 |
| TABLE1     | TAB1_COL_4 | TABLE 1 COLUMN 4 |
| TABLE1     | TAB1_COL_3 | TABLE 1 COLUMN 3 |
| TABLE1     | TAB1_COL_5 |                  |
| TABLE1     | TAB1_COL_1 | TABLE 1 COLUMN 1 |
----------------------------------------------

TABLE1
--------------------------------------------------------------------
| TAB1_COL_3      | TAB1_COL_1    | TAB1_COL_5     | TAB1_COL_2    |
--------------------------------------------------------------------
| TAB1_COL3_DATA1 | TAB1_COL1_DAT | TAB1_COL5_DAT2 | TAB1_COL2_DAT |
| TAB1_COL3_DATA2 | TAB1_COL1_DAT | TAB1_COL5_DAT1 | TAB1_COL2_DAT |
| TAB1_COL3_DATA3 | TAB1_COL1_DAT | TAB1_COL5_DAT3 | TAB1_COL2_DAT |
--------------------------------------------------------------------
  

Я хочу отобразить данные в виде 2 разных выходных данных:

 FIRST OUTPUT:
------------------------------------------------------------------------------------------------
| TABLE 1 COLUMN 3 | TABLE 1 COLUMN 1 | TAB1_COL_5       | TABLE 1 COLUMN 2 | TABLE 1 COLUMN 4 |
------------------------------------------------------------------------------------------------
  

-> В случае, если значение COL_DESC является пустым или нулевым, то в выходных данных необходимо отобразить COL_NAME.
-> «ТАБЛИЦА 1 СТОЛБЕЦ 3» И «ТАБЛИЦА 1 СТОЛБЕЦ 1» всегда должны отображаться как 1-й и 2-й столбцы, за которыми следуют остальные столбцы.
-> В случае, если какой-либо столбец, определенный в таблице COL_TAB, не используется в TABLE1, тогда такой столбец должен отображаться в последнем столбце выходных данных,
например, TAB1_COL_4 не используется в TABLE1, поэтому он отображается в последней.

 SECOND OUTPUT:
------------------------------------------------------------------------------------------------
| TAB1_COL3_DATA1  | TAB1_COL1_DAT    | TAB1_COL5_DAT2   | TAB1_COL2_DAT    |                  |
| TAB1_COL3_DATA2  | TAB1_COL1_DAT    | TAB1_COL5_DAT1   | TAB1_COL2_DAT    |                  |
| TAB1_COL3_DATA3  | TAB1_COL1_DAT    | TAB1_COL5_DAT3   | TAB1_COL2_DAT    |                  |
------------------------------------------------------------------------------------------------
  

-> Порядок СТОЛБЦОВ во ВТОРОМ ВЫВОДЕ должен быть синхронизирован с порядком столбцов, отображаемым в ПЕРВОМ ВЫВОДЕ.

Я попробовал приведенный ниже запрос для отображения ПЕРВОГО ВЫВОДА, но он не работает (я уверен, что это неверно):

 SELECT NVL(COL_DESC, COL_NAME) AS COL_TEXT
FROM COL_TAB
WHERE TABLE_NAME = 'TABLE1'
PIVOT(MIN(COL_TEXT)
FOR COL_TEXT IN (SELECT COL_NAME FROM COL_TAB WHERE TABLE_NAME = 'TABLE1'));
  

В случае, если что-то не ясно, пожалуйста, дайте мне знать. Я бы постарался объяснить это еще раз. Еще раз заранее спасибо за вашу помощь.

Вы можете получить описание / имена столбцов — в детерминированном порядке — с помощью чего-то вроде:

 select coalesce(ct.col_desc, ct.col_name)
from col_tab ct
left join user_tab_columns utc
on utc.table_name = ct.table_name and utc.column_name = ct.col_name
where ct.table_name = 'TABLE1'
order by utc.column_id, ct.col_name;
  

 COALESCE(CT.COL_
----------------
TABLE 1 COLUMN 3
TABLE 1 COLUMN 1
TAB1_COL_5
TABLE 1 COLUMN 2
TABLE 1 COLUMN 4
  

Преобразование этих строк в столбцы должно выполняться динамически.

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

Это использует SQL * Plus (или SQLcl, или SQL Developer) для привязки переменных курсоров ссылок для получения двух выходных данных и использует имя таблицы, определенное в блоке; но может быть легко адаптировано для процедуры, которой передается имя таблицы и есть параметры out для курсоров ссылок:

 var rc1 refcursor;
var rc2 refcursor;

declare
  l_table_name varchar2(30) := 'TABLE1';
  l_stmt varchar2(4000);
begin
  select 'select '
    || listagg('''' || coalesce(ct.col_desc, ct.col_name) || '''',  ',')
         within group (order by utc.column_id, ct.col_name)
    || ' from dual'
  into l_stmt
  from col_tab ct
  left join user_tab_columns utc
  on utc.table_name = ct.table_name and utc.column_name = ct.col_name
  where ct.table_name = l_table_name;

  dbms_output.put_line(l_stmt);

  open :rc1 for l_stmt;

  select 'select '
    || listagg(coalesce(utc.column_name, 'null') || ' as ' || ct.col_name,  ',')
         within group (order by utc.column_id, ct.col_name)
    || ' from ' || l_table_name
  into l_stmt
  from col_tab ct
  left join user_tab_columns utc
  on utc.table_name = ct.table_name and utc.column_name = ct.col_name
  where ct.table_name = l_table_name;

  dbms_output.put_line(l_stmt);

  open :rc2 for l_stmt;

end;
/
  

Запуск блока получает dbms_output часть инструкций только для отладки, но может представлять интерес:

 select 'TABLE 1 COLUMN 3','TABLE 1 COLUMN 1','TAB1_COL_5','TABLE 1 COLUMN 2','TABLE 1 COLUMN 4' from dual
select TAB1_COL_3 as TAB1_COL_3,TAB1_COL_1 as TAB1_COL_1,TAB1_COL_5 as TAB1_COL_5,TAB1_COL_2 as TAB1_COL_2,null as TAB1_COL_4 from TABLE1
  

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

 print rc1

'TABLE1COLUMN3'  'TABLE1COLUMN1'  'TAB1_COL_ 'TABLE1COLUMN2'  'TABLE1COLUMN4' 
---------------- ---------------- ---------- ---------------- ----------------
TABLE 1 COLUMN 3 TABLE 1 COLUMN 1 TAB1_COL_5 TABLE 1 COLUMN 2 TABLE 1 COLUMN 4

print rc2

TAB1_COL_3      TAB1_COL_1    TAB1_COL_5     TAB1_COL_2    TAB1_COL_4
--------------- ------------- -------------- ------------- ----------
TAB1_COL3_DATA1 TAB1_COL1_DAT TAB1_COL5_DAT2 TAB1_COL2_DAT           
TAB1_COL3_DATA2 TAB1_COL1_DAT TAB1_COL5_DAT1 TAB1_COL2_DAT           
TAB1_COL3_DATA3 TAB1_COL1_DAT TAB1_COL5_DAT3 TAB1_COL2_DAT           
  

Эти 2 столбца являются общими для всех таблиц.

В этом случае вы можете использовать выражение case для расширения логики упорядочения:

          within group (order by case ct.col_name 
                                  when 'TAB1_COL_3' then 1
                                  when 'TAB1_COL_1' then 2
                                  else 3 end,
                                utc.column_id, ct.col_name)
  

который затем получает:

 'TABLE1COLUMN3'  'TABLE1COLUMN1'  'TAB1_COL_ 'TABLE1COLUMN2'  'TABLE1COLUMN4' 
---------------- ---------------- ---------- ---------------- ----------------
TABLE 1 COLUMN 3 TABLE 1 COLUMN 1 TAB1_COL_5 TABLE 1 COLUMN 2 TABLE 1 COLUMN 4


TAB1_COL_3      TAB1_COL_1    TAB1_COL_5     TAB1_COL_2    TAB1_COL_4
--------------- ------------- -------------- ------------- ----------
TAB1_COL3_DATA1 TAB1_COL1_DAT TAB1_COL5_DAT2 TAB1_COL2_DAT           
TAB1_COL3_DATA2 TAB1_COL1_DAT TAB1_COL5_DAT1 TAB1_COL2_DAT           
TAB1_COL3_DATA3 TAB1_COL1_DAT TAB1_COL5_DAT3 TAB1_COL2_DAT           
  

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

Было бы действительно здорово, если бы вы могли показать, как поворот может выполняться динамически.

в конце концов, здесь это на самом деле не нужно, и это сложнее, чем listagg я использовал выше; но вы могли бы сделать что-то вроде;

   select '
select * from (
  select row_number()
           over (order by case ct.col_name 
                            when ''TAB1_COL_3'' then 1
                            when ''TAB1_COL_1'' then 2
                            else 3
                          end,
                          utc.column_id, ct.col_name) as pos,
         coalesce(ct.col_desc, ct.col_name) as name
  from col_tab ct
  left join user_tab_columns utc
  on utc.table_name = ct.table_name and utc.column_name = ct.col_name
  where ct.table_name = :tab
)
pivot (max(name) as col for (pos) in ('
|| listagg(level, ',') within group (order by level)
|| '))'
  into l_stmt
  from dual
  connect by level <= (select count(*) from col_tab where table_name = l_table_name);

  dbms_output.put_line(l_stmt);

  open :rc1 for l_stmt using l_table_name;
  

который получает вывод, показывающий сгенерированный динамический запрос в виде:

 select * from (
  select row_number()
           over (order by case ct.col_name 
                            when 'TAB1_COL_3' then 1
                            when 'TAB1_COL_1' then 2
                            else 3
                          end,
                          utc.column_id, ct.col_name) as pos,
         coalesce(ct.col_desc, ct.col_name) as name
  from col_tab ct
  left join user_tab_columns utc
  on utc.table_name = ct.table_name and utc.column_name = ct.col_name
  where ct.table_name = :tab
)
pivot (max(name) as col for (pos) in (1,2,3,4,5))
  

и результирующий набор в виде:

 1_COL            2_COL            3_COL            4_COL            5_COL           
---------------- ---------------- ---------------- ---------------- ----------------
TABLE 1 COLUMN 3 TABLE 1 COLUMN 1 TAB1_COL_5       TABLE 1 COLUMN 2 TABLE 1 COLUMN 4
  

Вы могли бы использовать имена столбцов для сводной таблицы вместо pos , я думаю, это только усложнило бы чтение, так как вам нужно было бы заключить их в кавычки.