UNPIVOT syntax in Oracle:
SELECT ... FROM ... UNPIVOT [INCLUDE|EXCLUDE NULLS] (unpivot_clause unpivot_for_clause unpivot_in_clause ) WHERE ...
SQL> SELECT * 2 FROM pivoted_data 3 UNPIVOT ( 4 deptsal --<-- unpivot_clause 5 FOR saldesc --<-- unpivot_for_clause 6 IN (d10_sal, d20_sal, d30_sal, d40_sal) --<-- unpivot_in_clause 7 ); JOB SALDESC DEPTSAL ---------- ---------- ---------- CLERK D10_SAL 1430 CLERK D20_SAL 2090 CLERK D30_SAL 1045 SALESMAN D30_SAL 6160 PRESIDENT D10_SAL 5500 MANAGER D10_SAL 2695 MANAGER D20_SAL 3272.5 MANAGER D30_SAL 3135 ANALYST D20_SAL 6600
You can convert the rows and columns in the Polardb-O by using the interface of the Crosstab function.
An example of changing a row is as follows:
with a as ( -- A corresponds to the original data (that is, data whose columns need to be converted to rows) select js->>'seller' as seller, js->>'se_year' as se_year, jan , feb , mar , apr , may , jun , jul , aug , sep , oct , nov , dec from crosstab( -- This is the data source for row and column transformation. -- The sorting field is a group by field, the last field is the transformed content field, and the second field of the derivative is the row and column transformed field (the content is an enumeration, such as the month). -- (All corresponding enumerated values must be extracted in the next parameter.) $$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl_sellers_info group by 1,2 order by 1$$, -- Values are extracted as columns in rows converted from rows. This here represents the month, which is the value of se_month -- Or (select * from (values('jan'),...('dec')) t(se_month)) 'select distinct se_month from tbl_sellers_info order by 1' ) as -- crosstab output format (js jsonb, -- one or more fields corresponding to the order by clause in the SQL statement of the first parameter Jan numeric, -- the enumerated value of the second field of the corresponding derivative in the first parameter SQL, (row to column) feb numeric, -- ... Same as above mar numeric, apr numeric, may numeric, jun numeric, jul numeric, aug numeric, sep numeric, oct numeric, nov numeric, dec numeric ) order by 1,2 ) , -- b: use jsonb to merge multiple columns into one column and expand the columns by using jsonb_each. b as (select seller, se_year, jsonb_each(row_to_json(a)::jsonb-'seller'::text-'se_year'::text) as rec from a) select seller, se_year, (b.rec).key as month, (b.rec).value as sum from b;