Background information
UNPIVOT syntax in Oracle:
SELECT ...
FROM ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
(unpivot_clause
unpivot_for_clause
unpivot_in_clause )
WHERE ...
Example:
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
New solution
You can convert the rows and columns in the Polardb-O by using the interface of the Crosstab function.
Examples
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;