Share
Embed
Converting rows to columns and back again
How to use the pivot and unpivot clauses in Oracle Database to convert rows to columns and vice-versa.
Chris
January 19, 2021
Transcript
40 slides
-
Ask TOM Office Hours Convert Rows to Columns and Back
Pivot and unpivot Chris Saxon @ChrisRSaxon & @SQLDaily https://blogs.oracle.com/sql https://www.youtube.com/c/TheMagicofSQL Your SQL Office Hours begins soon… -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Turn rows into columns? Ryan McGuire / Gratisography -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Date Count 1 Jan 1,422 2 Jan 6,053 3 Jan 29,993 Jan Feb Mar 100,123 299,993 123,456 -
select * from … pivot ( aggregate_function for source_column in
( values ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Compute this For each of these -
select * from … pivot ( count (*) for customer_id
in ( 1, 2, 3 ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Must be a column group by … -
select * from … pivot ( count (*) for customer_id
in ( 1, 2, 3 ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Must be a column Implicit group by non-pivot cols group by … -
with rws as ( select … from … ) select
* from rws pivot ( aggregate_function for source_column in ( values ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Format and restrict cols -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL …but I need many aggregates Ryan McGuire / Gratisography -
with rws as ( select … from … ) select
* from rws pivot ( aggr_fn1 a1, aggr_fn2 a2, … for source_column in ( values ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Aliases! -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL …and pivot on many columns Ryan McGuire / Gratisography -
with rws as ( select … from … ) select
* from rws pivot ( aggregate_function for ( src_col1, src_col2 ) in ( ( v1c1, v1c2 ), ( v2c1, v2c2 ) ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon
* blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Dynamic pivot? Ryan McGuire / Gratisography -
with rws as ( select … from … ) select
* from rws pivot ( aggregate_function for source_column in ( select … ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-00936: missing expression -
XML Pivot IN query Output is in XML
Numbered cols Standard query Unchanging column names Dynamic SQL Correct columns Run two queries Dynamic Pivoting Options Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon
* blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Row-and- column totals? Ryan McGuire / Gratisography -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Year Jan Feb Mar Total 2018 0 18 73 91 2019 215 155 154 524 Total 215 173 227 615 -
select …, piv_c1 + piv_c2 + … from … pivot
( aggregate_function for source_column in ( values ) ) group by rollup ( … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Total column Total row -
with rws as ( select … from … group by
rollup ( … ), rollup ( … ) ) select * from rws pivot ( …, 'total' as tot ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon
* blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Columns back to rows? Ryan McGuire / Gratisography -
select * from … unpivot ( value_col for col_names in
( columns ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Unpivoted row values Must be same data type! Unpivoted column names -
with rws as ( select to_char ( … ) c1,
to_char ( … ) c2, from … ) select * from rws unpivot ( value_col for col_names in ( columns ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon
* blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Dynamic unpivot? Ryan McGuire / Gratisography -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Polymorphic Table Functions! https://livesql.oracle.com/apex/livesql/file/content_ GBB2BVOTJNDI1L4EUAWCQUBIB.html -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Switch rows and columns? Ryan McGuire / Gratisography -
select * from … unpivot ( … ) pivot (
… ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon
* blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Other uses? Ryan McGuire / Gratisography -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Event Start Date End Date Event 1 1 Jan 3 Jan Event 2 6 Jan 10 Jan Event 3 8 Jan 12 Jan unpivot -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Event Date Col Date Value Event 1 START_DATE 1 Jan Event 2 END_DATE 3 Jan Event 3 START_DATE 6 Jan Event 3 START_DATE 8 Jan Event 3 END_DATE 10 Jan Event 3 END_DATE 12 Jan -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Event Date Col Date Value Event 1 START_DATE 1 Jan Event 2 END_DATE 3 Jan Event 3 START_DATE 6 Jan Event 3 START_DATE 8 Jan Event 3 END_DATE 10 Jan Event 3 END_DATE 12 Jan Gap! Overlap! -
select pk, src_col, src_val from ( select pk, src_col, src_val,
0 old_c, 1 new_c from tab_1 unpivot ( … ) union all select pk, src_col, src_val, 1 old_c, 0 new_c from tab_2 unpivot ( … ) ) group by pk, src_col, src_val having sum ( old_c ) <> sum ( new_c ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL PK SRC COL SRC VAL 1 SOME_COL Black val 1 SOME_COL BLUE val 4 ANOTHER_COL OLD VAL 4 ANOTHER_COL NEW VAL 9 SOME_COL Missing row 9 ANOTHER_COL Missing row -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Further Reading How to Convert Rows to Columns and Back Again with SQL (Aka PIVOT and UNPIVOT) https://blogs.oracle.com/sql/how-to-convert-rows- to-columns-and-back-again-with-sql-aka-pivot-and- unpivot Stew Ashton's Blog https://stewashton.wordpress.com/ -
Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *
blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Ryan McGuire / Gratisography See you soon! asktom.oracle.com #AskTOMOfficeHours Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL