Through different combination of the query has the following fields of a query result: org, year, month, and the account, the balance
Now want to transfer the account column into a column, org, year, month remains the same, because not sure how much value, so there's no easy way to excuse me each master method of implementing the dynamic transfer line column? y

CodePudding user response:

The pivot, if the account number is not sure, need to use dynamic SQL

CodePudding user response:

reference 1st floor ZJHZ_ leaf response:

pivot, if the account number is not sure, need to use dynamic SQL

I also see a lot about the pivot line column, for dynamic SQL/stored procedure to realize line column this link may not always its meaning, can translate this piece of detailed said?

CodePudding user response:

I built a test, and then through the block to get the following statement, and the results you want?


Declare
Sin varchar2 (1024);
SSQL varchar2 (1024);
The begin
Select "' '| | listagg (account,' ' ' ' ' ') within group (order by an account) | | ' ' ' 'into sin from TABLE_TEST;
SSQL:='select * from TABLE_TEST a
The pivot (sum (balance) for the account (in '| | sin | |')) ';
Dbms_output. Put_line (SSQL);
END;

CodePudding user response:

reference ZJHZ_ leaf reply: 3/f

I built a test, and then through the block to get the following statement, and the results you want?


Declare
Sin varchar2 (1024);
SSQL varchar2 (1024);
The begin
Select "' '| | listagg (account,' ' ' ' ' ') within group (order by an account) | | ' ' ' 'into sin from TABLE_TEST;
SSQL:='select * from TABLE_TEST a
The pivot (sum (balance) for the account (in '| | sin | |')) ';
Dbms_output. Put_line (SSQL);
END;

Is such a mean but I finished the output instead of query results in a SQL statement

CodePudding user response:

Declare
Sin varchar2 (1024);
SSQL varchar2 (1024);
The begin
Select "' '| | listagg (accounting subjects,' ' ' ' ' ') WITHIN GROUP (ORDER BY accounting subject) | | ' ' ' 'into sin FROM
(select distinct accounting subjects from v_balance_yj);
SSQL:='select * from v_balance_yj a
The pivot (sum (final balance) for accounting subjects in (' | | sin | | ')) ';
Dbms_output. Put_line (SSQL);
END;

CodePudding user response:

Front-end display, you first splicing in the value of the inside, and then handed in, in which cannot be directly select

CodePudding user response:

refer to 6th floor ZJHZ_ leaf response:

Front-end display, you first splicing in the value of the inside, and then pass into, it cannot be directly in the select

How do you pass?

CodePudding user response:

The above is two ways to
Declare is to generate statements, execute generated statement is the result you want
& 1 is direct incoming value
Front end code how to realize I don't understand, I will only database

CodePudding user response:

refer to the eighth floor ZJHZ_ leaf response:

is one of two ways above
Declare is to generate statements, execute generated statement is the result you want
& 1 is direct incoming value
Front end code how to realize I don't understand, I will only database

& 1 the direct incoming value is how to do ah I can put the select "'" '| | listagg (accounting subjects,' ' ' ' ' ') WITHIN GROUP (ORDER BY accounting subject) | | ' ' ' 'into & amp; 1 the FROM
(select distinct accounting subjects from v_balance_yj)

CodePudding user response:

You put & amp; 1 to you to define variables, above the variable assignment should first is ok, this not is very similar to the query page filter box condition?

CodePudding user response:

Like this is quite troublesome