Group and pivot data by using queries

Exam 70-761: Querying Data with Transact-SQL retired on 31 January, 2021.

Ranking Functions (Transact-SQL)

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

Transact-SQL provides the following ranking functions:

RANK NTILE
DENSE_RANK ROW_NUMBER

SELECT – GROUP BY- Transact-SQL

A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.

OUTPUT Clause (Transact-SQL)

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view. Which is used in –

DELETE
INSERT
UPDATE
MERGE

output_table

Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

If column_list is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

output_table cannot:

Have enabled triggers defined on it.
Participate on either side of a FOREIGN KEY constraint.
Have CHECK constraints or enabled rules.

column_list

Is an optional list of column names on the target table of the INTO clause. It is analogous to the column list allowed in the INSERT statement.

scalar_expression

Is any combination of symbols and operators that evaluates to a single value. Aggregate functions are not permitted in scalar_expression. Any reference to columns in the table being modified must be qualified with the INSERTED or DELETED prefix.

column_alias_identifier

Is an alternative name used to reference the column name.

DELETED

Is a column prefix that specifies the value deleted by the update or delete operation. Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed.

DELETED cannot be used with the OUTPUT clause in the INSERT statement.

INSERTED

Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed. INSERTED cannot be used with the OUTPUT clause in the DELETE statement.

from_table_name

Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

Note, Specifies that all columns affected by the delete, insert, or update action will be returned in the order in which they exist in the table.

FROM – Using PIVOT and UNPIVOT

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.

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.

Note – 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.