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

Converting rows to columns and back again

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.

Chris

January 19, 2021
Tweet

More Decks by Chris

Other Decks in Technology

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