Kscope19 Logo-No Location.png

View Printable Version

Data Twisting

Session Number: 8145
Track: Database
Sub-Categorization: DB Dev
Session Type: Tips, Techniques and Tuning
Primary Presenter: Kim Berg Hansen [Senior Developer - Trivadis]
Time: Jun 25, 2019 (02:15 PM - 03:15 PM)
Room: 618, Level 6

Speaker Bio: Kim Berg Hansen is a database developer from Middelfart in Denmark. Originally wanting to work with electronics, he almost coincidentally tried computer programming and discovered where his talent lay; the programs he did worked well—unlike the electronics projects he soldered, which often failed. After that experience he progressed from Commodore Basic on VIC-20 over Modula-2 and C at Odense University to Oracle SQL and PL/SQL, which he has now worked with extensively since 2000. His professional passion is working with data inside the database, utilizing the SQL language to the fullest to achieve the best application experience for the users.

Kim shares his experience and knowledge by blogging, presenting at various Oracle User Group conferences, and being the SQL quizmaster at the Oracle Dev Gym. His motivation is when peers go “now I understand” after his explanations, or when end users “can’t live without” his application coding. He is a certified Oracle OCE in SQL, as well as an Oracle ACE Director. Outside the coding world, Kim is married, loves to cook, and is a card-carrying member of Danish Beer Enthusiasts association.
Technologies or Products Used: Oracle Database

Session Summary for Attendees:  Humans often can get a better overview of data when it is presented in columns. However, while working with datasets in SQL, it is easier having the data in rows. Being able to twist the data from columns to rows and vice versa is very useful. In this session, you’ll learn different ways to do the Data Twist.

The SELECT statement has built-in capabilities for doing this for a fixed number of columns—UNPIVOT to turn columns into rows, PIVOT to turn rows into columns. For older database versions, these maneuvers can be simulated using row generators for unpivoting and grouping for pivoting.

When the number of columns is not known, you’re typically dealing with data in a string separated by some delimiter like a comma, semicolon, or tab. Other techniques can be used for turning separated data into rows and turning rows into separated data.

Be sure to keep your user profile up to date! Click here to update now.