Oracle transfer

Cust prod
1 A
1 B
1 C
2 A
2 B

Return
Cust Prod1 Prod2 Prod3
1 A B C
2 A B Null

How do ah

Started by Winston at February 03, 2016 - 10:03 AM

Slowly applied
select Cust,
MAX(decode(prod,'A','A')) Prod1, 
MAX(decode(prod,'B','B')) Prod2,
MAX(decode(prod,'C','C')) Prod3
from ax
GROUP BY Cust;

      CUST PROD1 PROD2 PROD3
---------- ----- ----- -----
         1 A     B     C
         2 A     B     

Posted by Heidi at November 27, 2016 - 6:16 AM

To see whether the data is fixed if you are not sure if the line number can only use dynamic SQL judge also judge whether the same prod...

Posted by Rex at December 01, 2016 - 6:23 AM

Not that the value of decode. ABC is just for example. This is not true

Posted by Winston at December 14, 2016 - 6:34 AM

to 2f
So the design of dynamic statement.

Posted by Winston at December 19, 2016 - 7:11 AM

Don't judge whether the prod the same. The number of rows is uncertain

Posted by Winston at December 30, 2016 - 7:13 AM

Create table test insertion test data:
create table tb1 (cust number,prod nvarchar2(5));
insert into tb1 values (1,'A');
insert into tb1 values (1,'B');
insert into tb1 values (1,'C');
insert into tb1 values (2,'A');
insert into tb1 values (2,'B');
commit;   

Create a stored procedure
CREATE OR REPLACE PROCEDURE T_TEST
IS
  V_SQL NVARCHAR2(2000); 
  CURSOR CURSOR_1 IS
  SELECT DISTINCT PROD 
  FROM TB1
  ORDER BY PROD;
 
    BEGIN
      V_SQL := 'SELECT CUST ';
      FOR V_TB IN CURSOR_1
      LOOP
        V_SQL := V_SQL || ',' || 'MAX(DECODE(T.PROD,'''|| V_TB.PROD || ''',T.PROD,NULL)) AS PROD_'|| V_TB.PROD ||'';
      END LOOP;
      V_SQL := V_SQL || ' FROM  TB1 T' ;
      V_SQL := V_SQL || ' GROUP BY CUST';
     DBMS_OUTPUT.PUT_LINE(V_SQL);
    --EXECUTE IMMEDIATE V_SQL;
    END;


- execute a stored procedure that returns the result of SQL execution

     CUST   PROD_A  PROD_B  PROD_C
-------------------------------------------
1	1	A	B	C
2	2	A	B	

-- continue to insert the test data
insert into tb1 values (2,'C');
insert into tb1 values (2,'D');
commit;  

- execute a stored procedure that returns the query

     CUST   PROD_A  PROD_B  PROD_C  PROD_D
-------------------------------------------
1	1	A	B	C	
2	2	A	B	C	D

- delete test and test procedure
--DROP TABLE TB1
--DROP PROCEDURE T_TEST

Posted by Rex at December 31, 2016 - 7:51 AM

7, Dynamic to indefinite column
The new test list situation
CREATE TABLE tmp_user_2(USER_ID NUMBER,MODE_NAME VARCHAR2(100),TYPE_ID NUmBER);

The first part of the test data of situation
INSERT INTO tmp_user_2 VALUES(1001, 'M1',1); 
INSERT INTO tmp_user_2 VALUES(1001, 'M2',2);
INSERT INTO tmp_user_2 VALUES(1002, 'M1',3);
INSERT INTO tmp_user_2 VALUES(1002, 'M2',4);
INSERT INTO tmp_user_2 VALUES(1002, 'M3',5);
INSERT INTO tmp_user_2 VALUES(1003, 'M1',6);
COMMIT;

Situation to column storage process
CREATE OR REPLACE PROCEDURE P_tmp_user_2 IS
  V_SQL VARCHAR2(2000);
  CURSOR CURSOR_1 IS
    SELECT DISTINCT T.MODE_NAME FROM tmp_user_2 T ORDER BY MODE_NAME;

BEGIN
  V_SQL := 'SELECT USER_ID';
  FOR V_XCLCK IN CURSOR_1 LOOP
    V_SQL := V_SQL || ',' || 'SUM(DECODE(MODE_NAME,''' || V_XCLCK.MODE_NAME ||
             ''',TYPE_ID,0)) AS ' || V_XCLCK.MODE_NAME;
  END LOOP;

  V_SQL := V_SQL || ' FROM tmp_user_2 GROUP BY USER_ID';
  --DBMS_OUTPUT.PUT_LINE(V_SQL);
  V_SQL := 'CREATE OR REPLACE VIEW tmp_user_3  AS ' || V_SQL;
  --DBMS_OUTPUT.PUT_LINE(V_SQL);
  EXECUTE IMMEDIATE V_SQL;
END;

Situation to execute the stored procedure
BEGIN
  P_tmp_user_2;               
END;

To view the situation
SELECT * FROM tmp_user_3;

The second part test data situation
INSERT INTO tmp_user_2 VALUES(1003, 'M2',7); 
INSERT INTO tmp_user_2 VALUES(1004, 'M5',8); 
COMMIT;

Situation to execute the stored procedure
BEGIN
  P_tmp_user_2;               
END;
To view the situation
SELECT * FROM tmp_user_3;



Posted by Don at January 06, 2017 - 8:03 AM

http://wenku.baidu.com/view/d63a5d19ff00bed5b9f31de1.html

Columns or rows are not fixed are generally write a cursor, inside you traverse the column or row, assembling the dynamic SQL statements

Upstairs are all dynamic SQL package to a try in, can also be directly returns a result set, the direct use.

Posted by Heidi at January 07, 2017 - 9:27 AM

To HJ_daxian

I compiled stored procedures like you, the implementation is successful, but not return output results

Sqlplus /nolog
Conn username/password@dbname
The connected
SQL> BEGIN
2 SP_TEST_ROW_TO_COL;
3 COMMIT;
4 END;
5 /

PL/The SQL process has been successfully completed.

SQL>

Thus, there is no data output

Posted by Winston at January 13, 2017 - 8:35 AM

I is the implementation of the return SQL finally only the mosaic of the dynamic SQL statement prints LZ can be modified to perform the SQL data set

Posted by Rex at January 13, 2017 - 8:57 AM