Tweet
Tweet

Slide 1

Slide 1 text

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…

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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 …

Slide 6

Slide 6 text

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 …

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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!

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

select * from … unpivot ( … ) pivot ( … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 30

Slide 30 text

DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

Date Gaps and Overlaps

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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!

Slide 36

Slide 36 text

Column-Level Table Comparison

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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/

Slide 40

Slide 40 text

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