Pivot and UnPivot

Question 1

Of the three elements you should identify for a PIVOT, which one is not actually included in the PIVOT expression? Why? How can it be remedied?

Answer 1

• Grouping Element The grouping element is not specified. It is identified by elimination. Whatever is left in the query table besides the aggregation and spreading elements is used to group.
• Work Around Since you can’t specify the grouping element, create a CTE with only the columns you care about, for the PIVOT expression, to run against. By doing this your data will not be grouped on columns that are unnecessary for your purposes.

SQL Server 70-461 05-02

Question 2

How does the FOR <spreading column> IN (<distinct spreading values>) work?

Answer 2

• spreading column: The single column that holds rows of data that you would like to represent multiple columns in the pivot query. Shipperid, for example.
• distinct spreading values: Literally spells out what you want the column names to be. They have to represent values you would find in the spreading column.

SQL Server 70-461 05-02

Question 3

What if the distinct spreading values are irregular?

Answer 3

• They must be delimited.
• Example: IN([1], [2], [3])

SQL Server 70-461 05-02

Question 4

How many aggregate functions can you use in PIVOT?

Answer 4

Only 1

SQL Server 70-461 05-02

Question 5

Can the IN clause in PIVOT take a dynamic list of values?

Answer 5

• No. It only accepts a static list. A subquery cannot be used.
• You could potentially use dynamic SQL to address this.

SQL Server 70-461 05-02

Question 6

What aggregate function can’t be used with PIVOT? And what is the work around?

Answer 6

• Count(*) is not allowed
• Count(column name) is allowed
• If you want something similar to count(*), define a column in the CTE that has a 1 in every row. Name it agg_col, for example. In the PIVOT where you specify <aggregate function>(<aggregate column>) do COUNT(agg_col) to simulate count(*).

SQL Server 70-461 05-02

Question 7

What is the assigned data type to the values column you define when unpivoting?

Answer 7

Same as it was when pivoted.

SQL Server 70-461 05-02

Question 8

What three elements need to be identified in a PIVOT query?

Answer 8

1. On Rows or Grouping Element: What you want to see on rows
2. On Cols or Spreading Element: What you want to see on columns
3. Data or Aggregation Element: What you want to see at the intersection of each distinct row and column

SQL Server 70-461 05-02

Question 9

General recommended form of a PIVOT query

Answer 9

--Create a CTE with only the columns needed for your PIVOT
WITH PivotData AS
(
SELECT
<grouping column>,
<spreading column>,
<aggregation column>
FROM <table name>
)

SELECT <select list> 
FROM PivotData
PIVOT(
<aggregate function>(<aggregate columns>)
FOR <spreading column>
IN(<distinct spreading values>)
) AS P;

select list= name of grouping column and names of value columns. Ex. (custid, [1], [2], [3])

SQL Server 70-461 05-02

Question 10

Of the three PIVOT elements, which two cannot directly be a result of an expression and what is the work around for this?

Answer 10

• Aggregation and Spreading Elements: Cannot be a direct result of an expression
• Work Around: You could apply expression in the CTE, assign aliases to those expressions and refer to them in PIVOT.

SQL Server 70-461 05-02

Question 11

What does it mean that PIVOT and UNPIVOT are table operators?

Answer 11

1. It means they operate on the input to the left of them in the FROM clause.
2. The input could be a table, a CTE, multiple tables connected by joins.
3. Also, the result of the PIVOT can be used as the input to another table or tables to the right of it connected by joins.

SQL Server 70-461 05-02

Question 12

What is the basic format for the UNPIVOT statement?

Answer 12

--General form to UNPIVOT data 
SELECT 
<column list>, 
<names column>, 
<values column> 

FROM <table name>
UNPIVOT(<values column>
FOR<names column>
IN(<source columns>) 
) AS u;

You will know the source column names since they already exist. When you UNPIVOT them you have to choose the names column name which will list what were column names in PIVOT.

You will also have to decide values column name which will list what used to be aggregate value in PIVOT.

SQL Server 70-461 05-02

Question 13

Example of an UNPIVOT statement using test data from the book

Answer 13

--Example to UNPIVOT data 
SELECT 
custid,  --column list
shipperid,  --names column
freight --values column

FROM PivotData --table name
UNPIVOT(
freight --values column
FOR shipperid --names column
IN([1], [2], [3])
) 
) AS u;

You will know the source column names since they already exist. When you UNPIVOT them you have to choose the names column name which will list what were column names in PIVOT.

You will also have to decide values column name which will list what used to be aggregate value in PIVOT.

SQL Server 70-461 05-02

Question 14

What is the assigned data type to the names column you define when unpivoting?

Answer 14

nvarchar(128)

SQL Server 70-461 05-02