Then we can then apply the PIVOT operator on it. But to do so we have to first fetch the required data (called base data) from the tables using SELECT query and store it in a temporary table (called derived table). Headphone, Mobile, Tablet) so that it can hold the count of products (4, 2, 6) in each category. The PIVOT operator as mentioned in the introduction will turn the table around on the category_name column value (i.e. We can do so with the help of the PIVOT operator. Now suppose we want the above data in the below format with the number of products under each category. The resultset of the above query is the following. p and c are table aliases for products and categories tables and product_count is the column alias for the aggregated product_id column. By doing so it determines the number of products available for each category. The INNER JOIN retrieves the values of the category_name column from the categories table. We can do so with the help of the following query which uses the GROUP BY function on the category_name column and count aggregate function on the product_id column. we want to find out how many types of mobiles, how many types of headphones do we have in our stock for sale. Suppose we want to get the count of products belonging to the different categories i.e. We will use them as our reference for the examples. The categories table contains some category names to which the products belong and the products table contains the names of products belonging to one or another category. Let us consider two tables – categories and products. We will try to understand the above PIVOT query and operation with the help of an example. value –static data values of the pivoted column.PIVOT – SQL operator for pivoting a column making the column values containers holding aggregate values.alias4 – alias for the output of the PIVOT operation.alias3 – table alias for the inner SELECT query which acts as a base table for the outer SELECT query.alias1/alias2 – table alias names for table1 and table2.table1 – the first table in the JOIN from which all or most of the final resultset values come.aggregate_function (column) – column on which aggregate function (i.e.additional_column – any column in the SELECT list apart from the column which is pivoted and the column which is aggregated.pivot_column – the column on which the PIVOT operation is performed.The PIVOT clause which maps the aggregated values of the column chosen for aggregation against the values of the pivoted column.The inner SELECT query within the outer SELECT which returns the base data from the table used by the rest of the query.The query above can be divided into 3 parts. The basic syntax of the PIVOT operator can be represented as below. Any other column specified in the SELECT query form row groups in the pivot output. The columns created by PIVOT hold aggregate values. As a result, those data values become the columns (or column headings) in the rotated table. The PIVOT operator uses the data values of a column called the PIVOT column and turns the table around it. It is used in SELECT statements to format the output around the values of a particular column or field.Ī query resultset is in the form of a table with rows and columns. It is a tool used to format the output of a query resultset so that it can presented in a spreadsheet form for reporting purposes. a central point) to organize query resultset. The PIVOT operator in SQL is so called because it acts as a PIVOT (i.e. PIVOT literally means the central point around which something revolves.
#Sql server unpivot how to#
The objective of this SQL Server tutorial is to teach you how to use the PIVOT operator to format the output of a SQL query around the values of a table column.