1Before PIVOT:          After PIVOT:
2Year  Quarter  Sales   Year   Q1     Q2     Q3     Q4
32013  1        100     2013   100    200    150    300
42013  2        200     2014   120    220    170    280
52013  3        150
62013  4        300
7...

1SELECT *
2FROM (
3    -- Source query
4    SELECT Year, Quarter, Sales
5    FROM SalesTable
6) AS SourceTable
7PIVOT (
8    SUM(Sales)                    -- Aggregate function
9    FOR Quarter IN ([1], [2], [3], [4])  -- Values to become columns
10) AS PivotTable;

1SELECT *
2FROM (
3    SELECT 
4        pc.Name AS Category,
5        YEAR(soh.OrderDate) AS OrderYear,
6        sod.LineTotal
7    FROM Sales.SalesOrderDetail sod
8    JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
9    JOIN Production.Product p ON sod.ProductID = p.ProductID
10    JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
11    JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
12) AS SourceData
13PIVOT (
14    SUM(LineTotal)
15    FOR OrderYear IN ([2011], [2012], [2013], [2014])
16) AS PivotTable
17ORDER BY Category;

1SELECT *
2FROM (
3    SELECT 
4        st.Name AS Territory,
5        MONTH(soh.OrderDate) AS OrderMonth,
6        soh.TotalDue
7    FROM Sales.SalesOrderHeader soh
8    JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
9    WHERE YEAR(soh.OrderDate) = 2013
10) AS SourceData
11PIVOT (
12    SUM(TotalDue)
13    FOR OrderMonth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
14) AS PivotTable;

1DECLARE @Columns NVARCHAR(MAX);
2DECLARE @SQL NVARCHAR(MAX);
3
4-- Get unique years dynamically
5SELECT @Columns = STRING_AGG(QUOTENAME(OrderYear), ', ')
6FROM (
7    SELECT DISTINCT YEAR(OrderDate) AS OrderYear
8    FROM Sales.SalesOrderHeader
9) AS Years;
10
11-- Build dynamic SQL
12SET @SQL = N'
13SELECT *
14FROM (
15    SELECT 
16        pc.Name AS Category,
17        YEAR(soh.OrderDate) AS OrderYear,
18        sod.LineTotal
19    FROM Sales.SalesOrderDetail sod
20    JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
21    JOIN Production.Product p ON sod.ProductID = p.ProductID
22    JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
23    JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
24) AS SourceData
25PIVOT (
26    SUM(LineTotal)
27    FOR OrderYear IN (' + @Columns + ')
28) AS PivotTable
29ORDER BY Category;';
30
31EXEC sp_executesql @SQL;

1Before UNPIVOT:         After UNPIVOT:
2Product  Q1    Q2       Product  Quarter  Sales
3Bikes    100   200      Bikes    Q1       100
4Parts    150   180      Bikes    Q2       200
5                        Parts    Q1       150
6                        Parts    Q2       180

1SELECT *
2FROM (
3    SELECT Product, Q1, Q2, Q3, Q4
4    FROM QuarterlySales
5) AS SourceTable
6UNPIVOT (
7    Sales FOR Quarter IN (Q1, Q2, Q3, Q4)
8) AS UnpivotTable;

1-- First create a pivoted temp table
2WITH PivotedData AS (
3    SELECT *
4    FROM (
5        SELECT 
6            pc.Name AS Category,
7            YEAR(soh.OrderDate) AS OrderYear,
8            sod.LineTotal
9        FROM Sales.SalesOrderDetail sod
10        JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
11        JOIN Production.Product p ON sod.ProductID = p.ProductID
12        JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
13        JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
14    ) AS SourceData
15    PIVOT (
16        SUM(LineTotal)
17        FOR OrderYear IN ([2011], [2012], [2013], [2014])
18    ) AS PivotTable
19)
20-- Then UNPIVOT back
21SELECT Category, Year, Revenue
22FROM PivotedData
23UNPIVOT (
24    Revenue FOR Year IN ([2011], [2012], [2013], [2014])
25) AS UnpivotTable
26ORDER BY Category, Year;

1-- Traditional: 3 separate queries
2SELECT Category, NULL, SUM(Sales) FROM ... GROUP BY Category
3UNION ALL
4SELECT NULL, Year, SUM(Sales) FROM ... GROUP BY Year
5UNION ALL
6SELECT NULL, NULL, SUM(Sales) FROM ... -- Grand total
7
8-- With GROUPING SETS: single query
9SELECT Category, Year, SUM(Sales)
10FROM ...
11GROUP BY GROUPING SETS (
12    (Category),
13    (Year),
14    ()  -- Grand total
15);

1SELECT 
2    pc.Name AS Category,
3    YEAR(soh.OrderDate) AS Year,
4    SUM(sod.LineTotal) AS Revenue
5FROM Sales.SalesOrderDetail sod
6JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
7JOIN Production.Product p ON sod.ProductID = p.ProductID
8JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
9JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
10GROUP BY GROUPING SETS (
11    (pc.Name, YEAR(soh.OrderDate)),  -- Category + Year
12    (pc.Name),                        -- Category subtotal
13    (YEAR(soh.OrderDate)),            -- Year subtotal
14    ()                                -- Grand total
15)
16ORDER BY 
17    GROUPING(pc.Name),
18    GROUPING(YEAR(soh.OrderDate)),
19    pc.Name,
20    Year;

1SELECT 
2    pc.Name AS Category,
3    ps.Name AS Subcategory,
4    SUM(sod.LineTotal) AS Revenue
5FROM Sales.SalesOrderDetail sod
6JOIN Production.Product p ON sod.ProductID = p.ProductID
7JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
8JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
9GROUP BY ROLLUP (pc.Name, ps.Name)
10ORDER BY 
11    GROUPING(pc.Name),
12    pc.Name,
13    GROUPING(ps.Name),
14    ps.Name;

1Category    Subcategory      Revenue
2Bikes       Mountain Bikes   1000000
3Bikes       Road Bikes       800000
4Bikes       NULL             1800000  ← Category subtotal
5Clothing    Caps             50000
6Clothing    Jerseys          100000
7Clothing    NULL             150000   ← Category subtotal
8NULL        NULL             1950000  ← Grand total

1SELECT 
2    st.Name AS Territory,
3    YEAR(soh.OrderDate) AS Year,
4    SUM(soh.TotalDue) AS Revenue
5FROM Sales.SalesOrderHeader soh
6JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
7GROUP BY CUBE (st.Name, YEAR(soh.OrderDate))
8ORDER BY 
9    GROUPING(st.Name),
10    st.Name,
11    GROUPING(YEAR(soh.OrderDate)),
12    Year;

1SELECT 
2    pc.Name AS Category,
3    YEAR(soh.OrderDate) AS Year,
4    SUM(sod.LineTotal) AS Revenue,
5    GROUPING(pc.Name) AS Is_Category_Subtotal,
6    GROUPING(YEAR(soh.OrderDate)) AS Is_Year_Subtotal,
7    CASE 
8        WHEN GROUPING(pc.Name) = 1 AND GROUPING(YEAR(soh.OrderDate)) = 1 THEN 'Grand Total'
9        WHEN GROUPING(pc.Name) = 1 THEN 'Year Total'
10        WHEN GROUPING(YEAR(soh.OrderDate)) = 1 THEN 'Category Total'
11        ELSE 'Detail'
12    END AS RowType
13FROM Sales.SalesOrderDetail sod
14JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
15JOIN Production.Product p ON sod.ProductID = p.ProductID
16JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
17JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
18GROUP BY ROLLUP (pc.Name, YEAR(soh.OrderDate))
19ORDER BY GROUPING(pc.Name), pc.Name, GROUPING(YEAR(soh.OrderDate)), Year;

1-- Running average với different windows
2SELECT 
3    OrderDate,
4    TotalDue,
5    -- Running average (all previous)
6    AVG(TotalDue) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS RunningAvg,
7    -- 7-day moving average
8    AVG(TotalDue) OVER (ORDER BY OrderDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MovingAvg7Day
9FROM Sales.SalesOrderHeader
10WHERE YEAR(OrderDate) = 2013 AND MONTH(OrderDate) = 1
11ORDER BY OrderDate;

1-- Multiple levels of percentage
2SELECT 
3    pc.Name AS Category,
4    ps.Name AS Subcategory,
5    SUM(sod.LineTotal) AS Revenue,
6    -- % of category
7    CAST(SUM(sod.LineTotal) * 100.0 / 
8         SUM(SUM(sod.LineTotal)) OVER (PARTITION BY pc.Name) AS DECIMAL(5,2)) AS PctOfCategory,
9    -- % of grand total
10    CAST(SUM(sod.LineTotal) * 100.0 / 
11         SUM(SUM(sod.LineTotal)) OVER () AS DECIMAL(5,2)) AS PctOfTotal
12FROM Sales.SalesOrderDetail sod
13JOIN Production.Product p ON sod.ProductID = p.ProductID
14JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
15JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
16GROUP BY pc.ProductCategoryID, pc.Name, ps.ProductSubcategoryID, ps.Name
17ORDER BY pc.Name, Revenue DESC;

1WITH YearlySales AS (
2    SELECT 
3        pc.Name AS Category,
4        YEAR(soh.OrderDate) AS Year,
5        SUM(sod.LineTotal) AS Revenue
6    FROM Sales.SalesOrderDetail sod
7    JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
8    JOIN Production.Product p ON sod.ProductID = p.ProductID
9    JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
10    JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
11    GROUP BY pc.ProductCategoryID, pc.Name, YEAR(soh.OrderDate)
12)
13SELECT 
14    Category,
15    Year,
16    Revenue,
17    LAG(Revenue) OVER (PARTITION BY Category ORDER BY Year) AS PrevYearRevenue,
18    CASE 
19        WHEN LAG(Revenue) OVER (PARTITION BY Category ORDER BY Year) IS NULL THEN NULL
20        ELSE CAST((Revenue - LAG(Revenue) OVER (PARTITION BY Category ORDER BY Year)) * 100.0 /
21                  LAG(Revenue) OVER (PARTITION BY Category ORDER BY Year) AS DECIMAL(10,2))
22    END AS YoY_Growth_Pct
23FROM YearlySales
24ORDER BY Category, Year;

1-- Customer spending distribution
2WITH CustomerSpending AS (
3    SELECT 
4        c.CustomerID,
5        SUM(soh.TotalDue) AS TotalSpent
6    FROM Sales.Customer c
7    JOIN Sales.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID
8    GROUP BY c.CustomerID
9)
10SELECT 
11    CustomerID,
12    TotalSpent,
13    NTILE(100) OVER (ORDER BY TotalSpent) AS Percentile,
14    CUME_DIST() OVER (ORDER BY TotalSpent) AS CumulativeDistribution,
15    PERCENT_RANK() OVER (ORDER BY TotalSpent) AS PercentRank
16FROM CustomerSpending
17ORDER BY TotalSpent DESC;

1SELECT 
2    pc.Name AS Category,
3    COUNT(*) AS ProductCount,
4    AVG(p.ListPrice) AS AvgPrice,
5    STDEV(p.ListPrice) AS StdDevPrice,
6    VAR(p.ListPrice) AS VariancePrice,
7    MIN(p.ListPrice) AS MinPrice,
8    MAX(p.ListPrice) AS MaxPrice
9FROM Production.Product p
10JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
11JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
12WHERE p.ListPrice > 0
13GROUP BY pc.ProductCategoryID, pc.Name
14ORDER BY AvgPrice DESC;

1-- Median using PERCENTILE_CONT
2SELECT DISTINCT
3    pc.Name AS Category,
4    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY p.ListPrice) 
5        OVER (PARTITION BY pc.ProductCategoryID) AS MedianPrice,
6    AVG(p.ListPrice) OVER (PARTITION BY pc.ProductCategoryID) AS AvgPrice
7FROM Production.Product p
8JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
9JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
10WHERE p.ListPrice > 0;

1-- Correlation between ListPrice and OrderQuantity
2WITH ProductStats AS (
3    SELECT 
4        p.ProductID,
5        p.ListPrice,
6        AVG(sod.OrderQty * 1.0) AS AvgOrderQty
7    FROM Production.Product p
8    JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID
9    GROUP BY p.ProductID, p.ListPrice
10),
11Calculations AS (
12    SELECT 
13        COUNT(*) AS N,
14        SUM(ListPrice) AS SumX,
15        SUM(AvgOrderQty) AS SumY,
16        SUM(ListPrice * AvgOrderQty) AS SumXY,
17        SUM(ListPrice * ListPrice) AS SumX2,
18        SUM(AvgOrderQty * AvgOrderQty) AS SumY2
19    FROM ProductStats
20)
21SELECT 
22    (N * SumXY - SumX * SumY) / 
23    SQRT((N * SumX2 - SumX * SumX) * (N * SumY2 - SumY * SumY)) AS Correlation
24FROM Calculations;

1-- Find consecutive date ranges with sales
2WITH SalesDates AS (
3    SELECT DISTINCT CAST(OrderDate AS DATE) AS SaleDate
4    FROM Sales.SalesOrderHeader
5),
6DateGroups AS (
7    SELECT 
8        SaleDate,
9        DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY SaleDate), SaleDate) AS GroupDate
10    FROM SalesDates
11)
12SELECT 
13    MIN(SaleDate) AS PeriodStart,
14    MAX(SaleDate) AS PeriodEnd,
15    DATEDIFF(DAY, MIN(SaleDate), MAX(SaleDate)) + 1 AS DaysInPeriod
16FROM DateGroups
17GROUP BY GroupDate
18ORDER BY PeriodStart;

1-- First and last order per customer
2WITH OrderRanks AS (
3    SELECT 
4        CustomerID,
5        SalesOrderID,
6        OrderDate,
7        TotalDue,
8        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS FirstRank,
9        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS LastRank
10    FROM Sales.SalesOrderHeader
11)
12SELECT 
13    f.CustomerID,
14    f.SalesOrderID AS FirstOrderID,
15    f.OrderDate AS FirstOrderDate,
16    f.TotalDue AS FirstOrderAmount,
17    l.SalesOrderID AS LastOrderID,
18    l.OrderDate AS LastOrderDate,
19    l.TotalDue AS LastOrderAmount,
20    DATEDIFF(DAY, f.OrderDate, l.OrderDate) AS CustomerLifespanDays
21FROM OrderRanks f
22JOIN OrderRanks l ON f.CustomerID = l.CustomerID
23WHERE f.FirstRank = 1 AND l.LastRank = 1
24ORDER BY CustomerLifespanDays DESC;

1-- Group customer orders into sessions (orders within 30 days)
2WITH OrdersWithPrev AS (
3    SELECT 
4        CustomerID,
5        SalesOrderID,
6        OrderDate,
7        LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PrevOrderDate,
8        CASE 
9            WHEN DATEDIFF(DAY, 
10                LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate), 
11                OrderDate) > 30 
12            OR LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) IS NULL
13            THEN 1 ELSE 0 
14        END AS NewSession
15    FROM Sales.SalesOrderHeader
16),
17SessionGroups AS (
18    SELECT 
19        *,
20        SUM(NewSession) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS SessionID
21    FROM OrdersWithPrev
22)
23SELECT 
24    CustomerID,
25    SessionID,
26    COUNT(*) AS OrdersInSession,
27    MIN(OrderDate) AS SessionStart,
28    MAX(OrderDate) AS SessionEnd
29FROM SessionGroups
30GROUP BY CustomerID, SessionID
31HAVING COUNT(*) > 1
32ORDER BY CustomerID, SessionID;

1-- Tạo pivot report: Revenue theo Category (rows) và Quarter (columns) cho năm 2013
2
3-- YOUR CODE HERE

1-- Tạo report với subtotals: Territory → Year → Revenue
2-- Include subtotals per territory và grand total
3
4-- YOUR CODE HERE

1-- Tính YoY growth % cho mỗi Territory
2-- Hiển thị: Territory, Year, Revenue, PrevYearRevenue, GrowthPct
3
4-- YOUR CODE HERE