![Download Download](/uploads/1/2/5/2/125263029/945334043.jpg)
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.Explore Sarah Newman's board 'pivot. Quote Friends, Friends Cafe, 3 Friends, Funny Friend Quotes, Friends Moments, Friends Forever, Friend Memes,. Feb 18, 2018 - Pivot Animator 4.2.6 Now available. The beta version of the latest version of Pivot. This fixes a few bugs in v4.2.3 and adds icons to the figure.
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).Syntax
The following syntax summarizes how to use the
PIVOT
operator.Remarks
The column identifiers in the
UNPIVOT
clause follow the catalog collation. For SQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS
. For SQL Server 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.Basic PIVOT Example
The following code example produces a two-column table that has four rows.
Here is the result set.
No products are defined with three
DaysToManufacture
.The following code displays the same result, pivoted so that the
DaysToManufacture
values become the column headings. A column is provided for three [3]
days, even though the results are NULL
.Here is the result set.
Complex PIVOT Example
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.Here is a partial result set.
The results returned by this subselect statement are pivoted on the
EmployeeID
column.![Online Online](http://tse3.mm.bing.net/th?id=OGC.98e47beea1218a92e4bc55105c0a2481&pid=1.7&rurl=http%3a%2f%2f1.bp.blogspot.com%2f-kMjz3BY8Loo%2fTas5h1cQvjI%2fAAAAAAAAAE8%2fsq4QynWgy6g%2fs320%2fsuper%2banimation.gif&ehk=od3OuyPVCrUNO%2f%2bjIJZIqQ)
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.Important
When aggregate functions are used with
PIVOT
, the presence of any null values in the value column are not considered when computing an aggregation.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 Transact-SQL definition. Here is the query.Here is a partial result set.
Notice that
UNPIVOT
isn't the exact reverse of PIVOT
. PIVOT
carries out an aggregation and merges possible multiple rows into a single row in the output. UNPIVOT
doesn't reproduce the original table-valued expression result because rows have been merged. Also, null values in the input of UNPIVOT
disappear in the output. When the values disappear, it shows that there may have been original null values in the input before the PIVOT
operation.The
Sales.vSalesPersonSalesByFiscalYears
view in the AdventureWorks2012 sample database uses PIVOT
to return the total sales for each salesperson, for each fiscal year. To script the view in SQL Server Management Studio, in Object Explorer, locate the view under the Views folder for the AdventureWorks2012 database. Right-click the view name, and then select Script View as.See Also
FROM (Transact-SQL)
CASE (Transact-SQL)
CASE (Transact-SQL)
personagens para pivot 3.0 e 3.1
|