The existing table, the original data is as follows: Now you need to query the following results (conditions are: [80,~] Excellent, [60,80) pass, [~,60] failed):
The original table is converted with UNPIVOT, and the line changes to select * from CB_SHANGCICB UNPIVOT ("language", "math", "English"), and the results are as follows: The CJ is then matched with the band TT as (select * FROM Score Unpivot (CJ-KC in ("Language", "math", "English"))) Select KC, (case is cj>=80 then ' excellent ' when cj>=60 then ' Pass ' El Se ' failed ' end ' CJ from TT; execution results are as follows: Then use pivot for the column change select * FROM (with TT as (SELECT * from Score Unpivot (CJ for KC in ("Language", " Math "," English "))) Select KC, (case when cj>=80 then ' excellent ' when cj>=60 then ' pass ' else ' fail ' end) CJ from TT) PIVOT Max (CJ) for the KC in (' Language ' as ' language ', ' math ' as ' math ', ' English ' as ' English '), the red background part must be an aggregate function;
Row to column unpivot, column shift pivot, note that Oracle 11g and later support
