Data Twisting

Quite often you have data in rows and need them in columns, or vice versa. You can also often have delimited data in strings and need them separated out, or conversely aggregate rows of data into delimited strings. You can do the Data Twist in SQL, pivoting, unpivoting, splitting and aggregating.

Humans often can get a better overview of data when it is presented in columns, while working with the data in sets in SQL is easier having the data in rows. Being able to twist the data from columns to rows and vice versa is very useful. Learn different ways to do the Data Twist in this session:

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

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

Different techniques will be discussed and demonstrated:

• UNPIVOT with single or multi-column dimensions
• Unpivoting with row generators
• PIVOT with single or multi-column dimensions, with or without grouping
• Pivoting with GROUP BY and CASE
• Turning comma-separated data into rows
• ODCI dynamic table function parser
• LISTAGG to turn rows into comma-separated data
• Alternative methods for string aggregation