Skip to content

Commit d12d09e

Browse files
WilliamDAssafMSFTWilliamDAssafMSFT
committed
20230707 edit pass, add limitation
1 parent fb30987 commit d12d09e

1 file changed

Lines changed: 22 additions & 14 deletions

File tree

‎docs/t-sql/queries/from-using-pivot-and-unpivot.md‎

Lines changed: 22 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,8 @@ title: "Using PIVOT and UNPIVOT"
33
description: "Transact-SQL reference for PIVOT and UNPIVOT relational operators. Use these operators on SELECT statements to change a table-valued expression into another table."
44
author: VanMSFT
55
ms.author: vanto
6-
ms.date: "10/14/2019"
6+
ms.reviewer: wiassaf
7+
ms.date: 07/07/2023
78
ms.service: sql
89
ms.subservice: t-sql
910
ms.topic: reference
@@ -30,9 +31,10 @@ monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-s
3031

3132
You can use the `PIVOT` and `UNPIVOT` relational operators to change a table-valued expression into another table. `PIVOT` rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And `PIVOT` runs aggregations where they're required on any remaining column values that are wanted in the final output. `UNPIVOT` carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
3233

33-
The syntax for `PIVOT` provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of `SELECT...CASE` statements. For a complete description of the syntax for `PIVOT`, see [FROM (Transact-SQL)](../../t-sql/queries/from-transact-sql.md).
34+
The syntax for `PIVOT` is simpler and more readable than the syntax that may otherwise be specified in a complex series of `SELECT...CASE` statements. For a complete description of the syntax for `PIVOT`, see [FROM (Transact-SQL)](../../t-sql/queries/from-transact-sql.md).
3435

35-
## Syntax
36+
## Syntax
37+
3638
The following syntax summarizes how to use the `PIVOT` operator.
3739

3840
```syntaxsql
@@ -55,11 +57,14 @@ FOR
5557
<optional ORDER BY clause>;
5658
```
5759

58-
## Remarks
60+
## Remarks
61+
5962
The column identifiers in the `UNPIVOT` clause follow the catalog collation. For [!INCLUDE[ssSDS_md](../../includes/sssds-md.md)], the collation is always `SQL_Latin1_General_CP1_CI_AS`. For [!INCLUDE[ssNoVersion_md](../../includes/ssnoversion-md.md)] partially contained databases, the collation is always `Latin1_General_100_CI_AS_KS_WS_SC`. If the column is combined with other columns, then a collate clause (`COLLATE DATABASE_DEFAULT`) is required to avoid conflicts.
6063

64+
In [!INCLUDE [fabric](../../includes/fabric.md)] and [!INCLUDE [ssazuresynapse_md](../../includes/ssazuresynapse-md.md)] pools, queries with PIVOT operator fail if there's a GROUP BY on the nonpivot column output by PIVOT. As a workaround, remove the nonpivot column from the GROUP BY. Query results are the same, as this GROUP BY clause is duplicate.
6165

62-
## Basic PIVOT Example
66+
## Basic PIVOT example
67+
6368
The following code example produces a two-column table that has four rows.
6469

6570
```sql
@@ -110,7 +115,8 @@ Cost_Sorted_By_Production_Days 0 1 2 3 4
110115
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
111116
```
112117

113-
## Complex PIVOT Example
118+
## Complex PIVOT example
119+
114120
A common scenario where `PIVOT` can be useful is when you want to generate cross-tabulation reports to give a summary of the data. For example, suppose you want to query the `PurchaseOrderHeader` table in the `AdventureWorks2014` sample database to determine the number of purchase orders placed by certain employees. The following query provides this report, ordered by vendor.
115121

116122
```sql
@@ -129,7 +135,7 @@ FOR EmployeeID IN
129135
ORDER BY pvt.VendorID;
130136
```
131137

132-
Here is a partial result set.
138+
Here's a partial result set.
133139

134140
```
135141
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
@@ -151,11 +157,13 @@ FROM PurchaseOrderHeader;
151157
The unique values returned by the `EmployeeID` column become fields in the final result set. As such, there's a column for each `EmployeeID` number specified in the pivot clause: in this case employees `250`, `251`, `256`, `257`, and `260`. The `PurchaseOrderID` column serves as the value column, against which the columns returned in the final output, which are called the grouping columns, are grouped. In this case, the grouping columns are aggregated by the `COUNT` function. Notice that a warning message appears that indicates that any null values appearing in the `PurchaseOrderID` column weren't considered when computing the `COUNT` for each employee.
152158

153159
> [!IMPORTANT]
154-
> When aggregate functions are used with `PIVOT`, the presence of any null values in the value column are not considered when computing an aggregation.
160+
> When aggregate functions are used with `PIVOT`, the presence of any null values in the value column are not considered when computing an aggregation.
161+
162+
155163

156164
## UNPIVOT Example
157165

158-
`UNPIVOT` carries out almost the reverse operation of `PIVOT`, by rotating columns into rows. Suppose the table produced in the previous example is stored in the database as `pvt`, and you want to rotate the column identifiers `Emp1`, `Emp2`, `Emp3`, `Emp4`, and `Emp5` into row values that correspond to a particular vendor. As such, you must identify two additional columns. The column that will contain the column values that you're rotating (`Emp1`, `Emp2`,...) will be called `Employee`, and the column that will hold the values that currently exist under the columns being rotated will be called `Orders`. These columns correspond to the *pivot_column* and *value_column*, respectively, in the [!INCLUDE[tsql](../../includes/tsql-md.md)] definition. Here is the query.
166+
`UNPIVOT` carries out almost the reverse operation of `PIVOT`, by rotating columns into rows. Suppose the table produced in the previous example is stored in the database as `pvt`, and you want to rotate the column identifiers `Emp1`, `Emp2`, `Emp3`, `Emp4`, and `Emp5` into row values that correspond to a particular vendor. As such, you must identify two additional columns. The column that will contain the column values that you're rotating (`Emp1`, `Emp2`,...) will be called `Employee`, and the column that will hold the values that currently exist under the columns being rotated will be called `Orders`. These columns correspond to the *pivot_column* and *value_column*, respectively, in the [!INCLUDE[tsql](../../includes/tsql-md.md)] definition. Here's the query.
159167

160168
```sql
161169
-- Create the table and insert values as portrayed in the previous example.
@@ -180,7 +188,7 @@ UNPIVOT
180188
GO
181189
```
182190

183-
Here is a partial result set.
191+
Here's a partial result set.
184192

185193
```
186194
VendorID Employee Orders
@@ -202,7 +210,7 @@ Notice that `UNPIVOT` isn't the exact reverse of `PIVOT`. `PIVOT` carries out an
202210

203211
The `Sales.vSalesPersonSalesByFiscalYears` view in the [!INCLUDE[ssSampleDBobject](../../includes/sssampledbobject-md.md)] sample database uses `PIVOT` to return the total sales for each salesperson, for each fiscal year. To script the view in [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)], in **Object Explorer**, locate the view under the **Views** folder for the [!INCLUDE[ssSampleDBobject](../../includes/sssampledbobject-md.md)] database. Right-click the view name, and then select **Script View as**.
204212

205-
## See Also
206-
[FROM (Transact-SQL)](../../t-sql/queries/from-transact-sql.md)
207-
[CASE (Transact-SQL)](../../t-sql/language-elements/case-transact-sql.md)
208-
213+
## Next steps
214+
215+
- [FROM (Transact-SQL)](../../t-sql/queries/from-transact-sql.md)
216+
- [CASE (Transact-SQL)](../../t-sql/language-elements/case-transact-sql.md)

0 commit comments

Comments
 (0)