Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Converting rows to columns and back again

D7b6e701f0155fc189bbca6c89223b3c?s=47 Chris
January 19, 2021

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.

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

January 19, 2021
Tweet

Transcript

  1. 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…
  2. 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
  3. 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
  4. 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
  5. 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 …
  6. 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 …
  7. 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
  8. 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
  9. 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!
  10. 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
  11. 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
  12. DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  13. 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
  14. 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
  15. 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
  16. DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  17. 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
  18. 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
  19. 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
  20. 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
  21. DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  22. 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
  23. 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
  24. 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
  25. DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  26. 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
  27. 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
  28. 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
  29. select * from … unpivot ( … ) pivot (

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

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  31. 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
  32. Date Gaps and Overlaps

  33. 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
  34. 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
  35. 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!
  36. Column-Level Table Comparison

  37. 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
  38. 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
  39. 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/
  40. 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