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.

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