1 - ROW_NUMBER ()
ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
2 - RANK ()
RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.
Example :
SELECT [GROUP],FAMILY,CATEGORY,COMMODITY,PROJECTS
,RANK() OVER (ORDER BY CATEGORY) AS [RANK]
FROM [TABLE_NAME]
WHERE [COMMODITY] != '(CAT)'
ORDER BY 6
3 – DENSE_RANK ()
DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
Example:
SELECT [GROUP],FAMILY,CATEGORY,COMMODITY,PROJECTS
,RANK() OVER (ORDER BY CATEGORY) AS [RANK]
,DENSE_RANK()OVER (ORDER BY CATEGORY) AS [DENSE_RANK]
FROM [TABLE_NAME]
WHERE [COMMODITY] != '(CAT)'
ORDER BY 6
4 – NTILE ()
NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.
SELECT [GROUP],FAMILY,CATEGORY,COMMODITY,PROJECTS
,NTILE(4) OVER (ORDER BY PROJECTS) AS [NTILE]
FROM [TABLE_NAME]
WHERE [COMMODITY] != '(CAT)'
ORDER BY 6
5 – ROLLUP ()
Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.
Select
Case When GROUPING(Region) = 1 THEN 'All Regions'
Else Region End As Region,
Case When GROUPING(category) = 1 THEN 'All category'
Else category End As category,
SUM([Spend(USD)])As SPEND_Sum
From [TABLE_NAME]WHERE category != '(CAT)'
Group By Region,category
WITH Rollup
6– CUBE ()
Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.
Select
Case When GROUPING(Region) = 1 THEN 'All Regions'
Else Region End As Region,
Case When GROUPING(category) = 1 THEN 'All category'
Else category End As category,
SUM([Spend(USD)])As SPEND_Sum
From [TABLE_NAME]
WHERE category != '(CAT)'
Group By Region,category
WITH CUBE
Order By Case when Region = 'All Regions' Then 'ZZZZZZ' else Region end ASC,3 ASC
| CUBE | ROLLUP |
| It's an additional switch to GROUP BY clause. It can be applied to all aggregation functions to return cross tabular result sets. | It's an extension to GROUP BY clause. It's used to extract statistical and summarized information from result sets. It creates groupings and then applies aggregation functions on them. |
| Produces all possible combinations of subtotals specified in GROUP BY clause and a Grand Total. | Produces only some possible subtotal combinations. |
7– Pivot & UnPivot
What is a Pivot Table ?
A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.
Pivot tables display data in tabular form.
Pivot Table in sql grants the ability to display data in custom aggregations just like OLAP reports in SQL Server.
Example :
-- Creating a table
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM Product
GO
-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO
SELECT [GROUP],FAMILY,CATEGORY,COMMODITY,PROJECTS
,ROW_NUMBER() OVER (ORDER BY CATEGORY) AS [ROW_NUMBER]
FROM [TABLE_NAME]
