Pivoting Flashcards
What is it called when you turn rows into columns in your query result?
Pivoting
Last Revised: 4/2/21, 4.3
What is the process that gives you more control of the granularity of a query result and which columns are returned?
Pivoting
Last Revised: 4/2/21, 4.3
True or False: Pivoting is particularly useful for data models where each row of a table can be a different attribute, but you need those attributes as columns in a query result.
True
Last Revised: 4/2/21, 4.3
What should you do before you begin pivoting?
Determine what you want each column in you SELECT clause to return. Some columns may need pivoting, some may not.
(Last Revised: 4/2/21, 4.5)
One way to pivot data is by using the _______ statement inside of an aggregate function.
CASE
Last Revised: 4/2/21, 4.5
What will the syntax for each pivoted column in the SELECT clause look like?
AGGREGATE (CASE WHEN condition THEN true_value
ELSE other_value END) column_alias
(Last Revised: 4/2/21, 4.5)
What will the aggregate function be when pivoting aggregate functions with the CASE statement?
The same as the original aggregate function.
• You can determine this by defining what you want to return.
(Last Revised: 4/2/21, 4.6)
What will the condition be when pivoting aggregate functions with the CASE statement?
An = comparison with what is in the original GROUP BY clause.
(Last Revised: 4/2/21, 4.6)
What will the true_value be when pivoting aggregate functions with the CASE statement?
It will be what is in the original aggregate function
• It will be the column_name
(Last Revised: 4/2/21, 4.6)
What will the other_value be when pivoting aggregate functions with the CASE statement?
NULL
Last Revised: 4/2/21, 4.6
Can you use DISTINCT if you’re pivoting on the count aggregate function? If so, what will the syntax look like?
Yes
• DISTINCT would be added inside the parentheses just before CASE
(Last Revised: 4/2/21, 4.6)
Can you use CASE to pivot aggregate functions and non-aggregated data?
Yes
Last Revised: 4/2/21, 4.5, 4.8
What will the aggregate function be when pivoting non-aggregate data with the CASE statement?
MAX
Last Revised: 4/2/21, 4.10
What will the condition be when pivoting non-aggregate data with the CASE statement?
The = comparison that is in the original query
Last Revised: 4/2/21, 4.10
What will the true_value be when pivoting non-aggregate data with the CASE statement?
It will be what is in the SELECT clause
• In the example on 4.10 cte.Value is the true_value because those are the values of the columns we are pivoting.
(Last Revised: 4/2/21, 4.10)
What will the other_value be when pivoting non-aggregate data with the CASE statement?
NULL
Last Revised: 4/2/21, 4.10
True or False: When pivoting aggregate functions, the GROUP BY clause may not be necessary if you’re only interested in grand totals.
True
Last Revised: 4/2/21, 4.10
Do you need to have a GROUP BY clause when pivoting non-aggregate data?
Yes
• When pivoting non-aggregated data, you need to add the MAX aggregate function and therefore you need to have a GROUP BY clause.
(Last Revised: 4/2/21, 4.10)
There are two additional things to watch out for when pivoting non-aggregate data vs. pivoting aggregate functions. What are they?
- The GROUP BY clause
- LEFT OUTER JOINS
(Last Revised: 4/2/21, 4.10)
True or False: An alternative to a GROUP BY clause and a lot of aggregating in the main query is to do all of your pivoting in a subquery and then join to that subquery.
True
Last Revised: 4/2/21, 4.11
What is an alternative to pivoting using CASE statements?
Join to additional copies of a table
Last Revised: 4/2/21, 4.15
True or False: to pivot aggregate functions using copies of a table, you can use the same condition in your joins that you would otherwise use in the CASE statement.
True
Join to the table using a left outer join on the primary key and the condition
(Last Revised: 4/2/21, 4.15)
When pivoting aggregate functions using copies of a table how will the SELECT clause change?
The SELECT clause remain essentially the same, but needs to be duplicated for each set of conditions and tweaked to reference the correct table aliases.
(Last Revised: 4/2/21, 4.16)
True or False: to pivot non-aggregate functions using copies of a table, you can use the same condition in your joins that you would otherwise use in the CASE statement.
True
Last Revised: 4/2/21, 4.18
