Pivoting

Question 1

What is it called when you turn rows into columns in your query result?

Answer 1

Pivoting

Last Revised: 4/2/21, 4.3

Question 2

What is the process that gives you more control of the granularity of a query result and which columns are returned?

Answer 2

Pivoting

Last Revised: 4/2/21, 4.3

Question 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.

Answer 3

True

Last Revised: 4/2/21, 4.3

Question 4

What should you do before you begin pivoting?

Answer 4

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)

Question 5

One way to pivot data is by using the _______ statement inside of an aggregate function.

Answer 5

CASE

Last Revised: 4/2/21, 4.5

Question 6

What will the syntax for each pivoted column in the SELECT clause look like?

Answer 6

AGGREGATE (CASE WHEN condition THEN true_value
ELSE other_value END) column_alias

(Last Revised: 4/2/21, 4.5)

Question 7

What will the aggregate function be when pivoting aggregate functions with the CASE statement?

Answer 7

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)

Question 8

What will the condition be when pivoting aggregate functions with the CASE statement?

Answer 8

An = comparison with what is in the original GROUP BY clause.

(Last Revised: 4/2/21, 4.6)

Question 9

What will the true_value be when pivoting aggregate functions with the CASE statement?

Answer 9

It will be what is in the original aggregate function
• It will be the column_name

(Last Revised: 4/2/21, 4.6)

Question 10

What will the other_value be when pivoting aggregate functions with the CASE statement?

Answer 10

NULL

Last Revised: 4/2/21, 4.6

Question 11

Can you use DISTINCT if you’re pivoting on the count aggregate function? If so, what will the syntax look like?

Answer 11

Yes
• DISTINCT would be added inside the parentheses just before CASE

(Last Revised: 4/2/21, 4.6)

Question 12

Can you use CASE to pivot aggregate functions and non-aggregated data?

Answer 12

Yes

Last Revised: 4/2/21, 4.5, 4.8

Question 13

What will the aggregate function be when pivoting non-aggregate data with the CASE statement?

Answer 13

MAX

Last Revised: 4/2/21, 4.10

Question 14

What will the condition be when pivoting non-aggregate data with the CASE statement?

Answer 14

The = comparison that is in the original query

Last Revised: 4/2/21, 4.10

Question 15

What will the true_value be when pivoting non-aggregate data with the CASE statement?

Answer 15

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)

Question 16

What will the other_value be when pivoting non-aggregate data with the CASE statement?

Answer 16

NULL

Last Revised: 4/2/21, 4.10

Question 17

True or False: When pivoting aggregate functions, the GROUP BY clause may not be necessary if you’re only interested in grand totals.

Answer 17

True

Last Revised: 4/2/21, 4.10

Question 18

Do you need to have a GROUP BY clause when pivoting non-aggregate data?

Answer 18

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)

Question 19

There are two additional things to watch out for when pivoting non-aggregate data vs. pivoting aggregate functions. What are they?

Answer 19

1. The GROUP BY clause
2. LEFT OUTER JOINS

(Last Revised: 4/2/21, 4.10)

Question 20

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.

Answer 20

True

Last Revised: 4/2/21, 4.11

Question 21

What is an alternative to pivoting using CASE statements?

Answer 21

Join to additional copies of a table

Last Revised: 4/2/21, 4.15

Question 22

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.

Answer 22

True
Join to the table using a left outer join on the primary key and the condition

(Last Revised: 4/2/21, 4.15)

Question 23

When pivoting aggregate functions using copies of a table how will the SELECT clause change?

Answer 23

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)

Question 24

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.

Answer 24

True

Last Revised: 4/2/21, 4.18

Question 25

True or False: When pivoting non-aggregated data, you need to add the MAX aggregate function and therefore you need to have a GROUP BY clause.

Answer 25

True

Last Revised: 4/2/21, 4.18

Question 26

True or False: Pivoting non-aggregated data require multiple copies of a table?

Answer 26

False
• Pivoting non-aggregated data can be accomplished with grouping and aggregate function on CASE statements.

(Last Revised: 4/2/21, 4.21)

Question 27

Which aggregate functions can be used for pivoting the COUNT aggregate function?

Answer 27

COUNT or SUM

Last Revised: 4/2/21, 4.21

Question 28

Which aggregate function is typically used for pivoting non-aggregated data?

Answer 28

MAX
• MIN would also work. AVE would also work for numeric values.

(Last Revised: 4/2/21, 4.21)