Блог

Как написать SQL-запрос для приведенного ниже условия

Результат должен быть в запросе PL / SQL:

MY_TABLE и data выглядят следующим образом:

 |   sl. no | col1 | col2 | col3 |col4    | col5 ---col30| col41|col42|....col50
 ------    ------ ------  ------  ------ 
|    1001 |    50 |  101 |  12   |  40   |
|    1002 |    30 |  250 |  80   |       |
|    1003 |    40 |  150 |  90   |       |
|    1004 |    50 |  250 |  20   |       |
|    1005 |    70 |  300 |  30   | 50    |
|    1006 |    80 |  400 | 
  

col1, col2, col3, … col30

Я хочу получить col1..to данные col30 (значение) против sl.no (где условие равно sl.no ) если имеются данные col. Oracle 9I как строка только в PL / SQL результат oracle 9i выглядит так:

 1001 | 50
1001 | 101
1001 | 12
1001 | 40
1002 | 30 
1002 |250
1002 | 80  
1003 | 40
1003 | 150
1003 | 90
1004 | 50
1004 | 250
1004 | 20 
1005 |70
1005 |300
1005 |30
1005 |50
1006 |80
1006 |400
  

 select * 
from (
  select no, decode( rn, 1, col1, 2, col2, 3, col3, 4, col4, 5, col5, ...., 30, col30) col
  from table_name, (select rownum rn from all_objects where rownum <= 30 )
) 
where col is not null
  

или

 select * 
from (
  select no, decode( rn, 1, col1, 2, col2, 3, col3, 4, col4, 5, col5, ...., 30, col30) col
  from table_name, (select rownum rn from dual connect by rownum <= 30 )
) 
where col is not null
  

второй вариант лучше, но я не помню, будет ли он работать в 9i или нет

Чтобы отключить способ до 11g, вам нужно будет сделать что-то вроде:

 WITH my_table AS (SELECT 1001 sl_no, 50 col1, 101 col2, 12 col3, 40 col4 FROM dual UNION ALL
                  SELECT 1002 sl_no, 30 col1, 250 col2, 80 col3, NULL col4 FROM dual UNION ALL
                  SELECT 1003 sl_no, 40 col1, 150 col2, 90 col3, NULL col4 FROM dual UNION ALL
                  SELECT 1004 sl_no, 50 col1, 250 col2, 20 col3, NULL col4 FROM dual UNION ALL
                  SELECT 1005 sl_no, 70 col1, 300 col2, 30 col3, 50 col4 FROM dual UNION ALL
                  SELECT 1006 sl_no, 80 col1, 400 col2, NULL col3, NULL col4 FROM dual),
        dummy AS (SELECT LEVEL lvl
                  FROM   dual
                  CONNECT BY LEVEL <= 4 -- number of columns to unpivot
                  ),
       results AS (SELECT mt.sl_no,
                          d.lvl,
                          CASE WHEN d.lvl = 1 THEN 'COL1'
                               WHEN d.lvl = 2 THEN 'COL2'
                               WHEN d.lvl = 3 THEN 'COL3'
                               WHEN d.lvl = 4 THEN 'COL4'
                          END col_name,
                          CASE WHEN d.lvl = 1 THEN col1
                               WHEN d.lvl = 2 THEN col2
                               WHEN d.lvl = 3 THEN col3
                               WHEN d.lvl = 4 THEN col4
                          END col_value
                   FROM   my_table mt
                          CROSS JOIN dummy d)
SELECT sl_no,
       col_name,
       col_value
FROM   results
WHERE  col_value IS NOT NULL
ORDER BY sl_no, lvl;

     SL_NO COL_NAME  COL_VALUE
---------- -------- ----------
      1001 COL1             50
      1001 COL2            101
      1001 COL3             12
      1001 COL4             40
      1002 COL1             30
      1002 COL2            250
      1002 COL3             80
      1003 COL1             40
      1003 COL2            150
      1003 COL3             90
      1004 COL1             50
      1004 COL2            250
      1004 COL3             20
      1005 COL1             70
      1005 COL2            300
      1005 COL3             30
      1005 COL4             50
      1006 COL1             80
      1006 COL2            400
  

dummy Подзапрос используется для генерации набора строк с тем же числом, что и количество столбцов, которые необходимо отменить. В вашем примере это 4.

Затем мы перекрестно соединяем это с таблицей — это означает, что каждая строка в таблице дублируется 4 раза — по одному для каждого столбца, который не отображается.

Как только мы получим это, мы скажем: «отобразите первый столбец, который не будет выделен в первой строке, второй столбец во второй строке, третий столбец в третьей строке и т. Д.»

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

После того, как мы отключили столбцы, мы удаляем все значения, которые являются нулевыми.