Output Clause

Output Clause (T-SQL):

The OUTPUT clause in T-SQL is a powerful feature that allows you to retrieve information about rows affected by INSERT, UPDATE, DELETE, or MERGE statements. This information can be used for purposes like confirmation messages, auditing, or archiving. You can also insert the results into a table or table variable for further processing.

The OUTPUT clause can be used with the following SQL statements:

• INSERT
• UPDATE
• DELETE
• MERGE

When performing an INSERT operation, the OUTPUT clause can capture values from the newly inserted rows, such as identity column values or computed columns. Similarly, during a DELETE operation, it can retrieve details about the deleted rows. The syntax for the OUTPUT clause is as follows:

OUTPUT { [DELETED.] * | [INSERTED.] * | column_name [AS alias] }
INTO target_table

      

---

Analytic functions

Analytic Functions - SQL Server (T-SQL)

Analytic functions process a group of rows to compute aggregate values while preserving detailed insights. Unlike aggregate functions, which reduce groups to a single row, analytic functions can output multiple rows for each group. They are ideal for calculating metrics like moving averages, running totals, percentage distributions, or identifying top-N results within a group.

You may refer the link https://learn.microsoft.com/en-us/sql/t-sql/functions/analytic-functions-transact-sql?view=sql-server-ver16.

Different types of analytic functions in SQL Server:

• CUME_DIST:
  • Stands for cumulative distribution
  • Calculates the percentage of rows with values less than or equal to the current row's value.
  • Formula: text{CUME_DIST} = {Rank}/{Total Rows}
  • Values range from 0 to 1, inclusive.
• PERCENT_RANK:
  • Represents the percentage of rows with values less than the current row's value.
  • Formula: text{PERCENT_RANK} = (Rank - 1)/(Total Rows - 1)
  • Values range from 0 to 1, but the highest-ranked value is excluded.

Source Table	CUME DIST & PERCENT RANK

• PERCENTILE_CONT:
  • The PERCENTILE_CONT function calculates a percentile based on a continuous distribution of values. It is used to find the percentile value for a specified percentage within a group of rows. It may return a value that doesn't exist in the dataset, as it interpolates between values.
  • Sort the values in ascending order.
  • Formula: Position = (Percentile \times (Total Rows - 1)) + 1
  • If the position is an integer, return the value at that position and if the position is not an integer, interpolate between the two closest values.
• PERCENTILE_DISC:
  • The PERCENTILE_DISC function calculates a percentile based on a discrete distribution of values. It returns the value at the specified percentile, which must exist in the dataset.
  • Sort the values in ascending order.
  • Formula: Position = (Percentile \times (Total Rows - 1)) + 1
  • Return the value at that position. For example, if the salaries are {30.00, 40.00, 50.00}, the median would be 40.00.

Source Table	PERCENTILE CONT & DISC

• FIRST_VALUE:
  • Returns the first value in an ordered partition.
  • Formula: FIRST_VALUE(column_name) OVER (PARTITION BY column_name ORDER BY column_name)
  • Useful for retrieving the first occurrence of a value within a group.
• LAST_VALUE:
  • Returns the last value in an ordered partition.
  • Formula: LAST_VALUE(column_name) OVER (PARTITION BY column_name ORDER BY column_name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  • Useful for retrieving the last occurrence of a value within a group.

Source Table	First & Last Value

• LEAD:
  • Returns the value from a subsequent row in the result set.
  • Formula: LEAD(column_name, offset, default_value) OVER (PARTITION BY column_name ORDER BY column_name)
  • Useful for comparing the current row with a subsequent row.
• LAG:
  • Returns the value from a previous row in the result set.
  • Formula: LAG(column_name, offset, default_value) OVER (PARTITION BY column_name ORDER BY column_name)
  • Useful for comparing the current row with a previous row.

Source Table	LEAD & LAG

---

Semi Unpivot Data

In this example, we will demonstrate how to semi-unpivot data.

We have a source sheet / table containg "Date" and "9_A, 9_B, 9_C, 10_A, 10_B, 10_C" and so on columns. And here 9_A means Grade 9th and A Section.

Now our target is to unpivot this type of data into multiple columns where sections are columns and date and Grade will be rows

So if you see here we are not unpivoting all data we are unpivoting and also keep unpivoted data.

I have also seen very interesting topic related with unpivot using Cross Apply. You may refer the link https://www.mssqltips.com/sqlservertip/7835/unpivot-data-sql-server-cross-apply.

Input Data	Output Data

This is an example we'll trying to unpivot data using T-SQL

We will use the following query to unpivot data