查询oracle是否开启行移动,Oracle动态联接(或移动行)

我在跑步

甲骨文11g

并且需要将某些行分隔成列,但列的数量不确定,所以我需要动态地执行此操作。

最初我想分成多个查询,稍后再加入它们,但这似乎不是最合适的,而且我很难使其动态化。

这是我的表的一个简化示例:

CREATE TABLE foo (

id NUMBER,

cod NUMBER,

val NUMBER,

dat DATE

);

INSERT INTO foo VALUES(1, 35, 58.10, TO_DATE('01-07-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(2, 45, 38.50, TO_DATE('01-07-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(3, 45, 3.89, TO_DATE('20-07-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(4, 35, 102.0, TO_DATE('01-07-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(5, 75, 69.32, TO_DATE('01-07-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(6, 75, 74.65, TO_DATE('01-07-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(7, 45, 32.8, TO_DATE('01-07-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(8, 75, 12.76, TO_DATE('01-07-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(1, 35, 38.50, TO_DATE('01-08-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(2, 45, 3.89, TO_DATE('01-08-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(3, 45, 102.0, TO_DATE('01-08-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(4, 35, 69.32, TO_DATE('01-08-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(5, 75, 74.65, TO_DATE('01-08-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(6, 75, 32.8, TO_DATE('01-08-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(7, 45, 38.50, TO_DATE('30-08-2019', 'DD-MM-YYYY'));

INSERT INTO foo VALUES(8, 75, 3.89, TO_DATE('01-08-2019', 'DD-MM-YYYY'));

SELECT a.cod, a.val, b.cod, b.val

FROM foo a, (

SELECT id, cod, val

FROM foo

WHERE dat = TO_DATE('01-07-2019', 'DD-MM-YYYY')

) b

WHERE a.id = b.id AND

a.dat = TO_DATE('01-08-2019', 'DD-MM-YYYY');

输出(按cod排序):

COD|VAL |COD|VAL |

---|-----|---|-----|

35| 38.5| 35| 58.1|

35|69.32| 35| 102|

45| 3.89| 45| 38.5|

75|74.65| 75|69.32|

75| 32.8| 75|74.65|

75| 3.89| 75|12.76|

我需要这些行只有一个不同的cod,其他的将被安排在新的列中。

预期:

COD 35 | COD 45 | COD 75 |

-------------------|-------------------|-------------------|

COD|VAL |COD|VAL |COD|VAL |COD|VAL |COD|VAL |COD|VAL |

---|-----|---|-----|---|-----|---|-----|---|-----|---|-----|

35| 38.5| 35| 58.1| 45| 3.89| 45| 38.5| 75|74.65| 75|69.32|

35|69.32| 35| 102| | | | | 75| 32.8| 75|74.65|

| | | | | | | | 75| 3.89| 75|12.76|

谢谢

更新:

我目前能够动态创建列,但无法将它们之间的值关联起来。

DECLARE

p_cod_str VARCHAR2(100) := '35, 75, 45';

i_curr NUMBER(10) := 0;

tmp_prev VARCHAR2(10);

q_loop VARCHAR2(1000);

q_select VARCHAR2(1000);

q_where VARCHAR2(1000);

q_from VARCHAR2(5000);

q_query VARCHAR2(5000);

BEGIN

-- split cods string by comma

FOR i IN (

SELECT trim(regexp_substr(p_cod_str, '[^,]+', 1, LEVEL)) cod

FROM dual

CONNECT BY LEVEL <= regexp_count(p_cod_str, ',') + 1

) LOOP

q_loop := 'SELECT a.id, a.cod AS cod_a, a.val AS val_a, b.cod AS cod_b, b.val AS val_b

FROM foo a, (

SELECT id, cod, val

FROM foo

WHERE dat = TO_DATE(''01-07-2019'', ''DD-MM-YYYY'')

) b

WHERE a.id = b.id AND a.cod = ' || i.cod || ' AND

a.dat = TO_DATE(''01-08-2019'', ''DD-MM-YYYY'')

';

-- concat com os outros

IF (i_curr <> 0) THEN -- primeira passagem

q_where := q_where || ' AND t_' || tmp_prev || '.id = t_' || i.cod || '.id';

END IF;

q_select := q_select || ',

t_' || i.cod || '.cod_a, t_' || i.cod || '.val_a,

t_' || i.cod || '.cod_b, t_' || i.cod || '.val_b';

q_from := q_from || ', (' || q_loop || ') t_' || i.cod;

-- control

tmp_prev := i.cod;

i_curr := i_curr + 1;

END LOOP;

-- mount query

q_query := 'SELECT ' || ltrim(q_select, ', ')

|| ' FROM ' || ltrim(q_from, ', ');

--|| ' WHERE ' || ltrim(q_where, 'AND ');

dbms_output.put_line('query: ' || q_query);

END;