Penggunaan Pivot Pada Mysql

Berikut adalah cara penggunaan Pivot Pada Mysql

1. Buat Table, Isi data , Query data

1.1. Buat Table : Sales

Field :

id_trx, 

nama, 

tgl_trx, 

tim, 

nilai_trx

1.2. Isi data :

id_trx nama tgl_trx tim nilai_trx

1 Alfa 2016-01-10 1 250000

2 Charlie 2016-01-02 2 175000

3 Bravo 2016-01-01 1 310000

4 Bravo 2016-02-04 1 250000

5 Alfa 2016-01-15 1 300000

6 Charlie 2016-01-13 2 325000

7 Bravo 2016-02-07 1 275000

8 Bravo 2016-03-06 1 150000

9 Alfa 2016-02-05 1 215000

10 Alfa 2016-02-22 1 350000

11 Alfa 2016-02-02 1 450000

12 Alfa 2016-03-12 1 150000

13 Alfa 2016-03-17 1 225000

14 Bravo 2016-03-11 1 150000

15 Bravo 2016-03-18 1 150000

16 Charlie 2016-01-23 2 350000

17 Charlie 2016-02-22 2 300000

18 Charlie 2016-03-21 2 275000

19 Charlie 2016-03-12 2 450000

20 Delta 2016-02-11 2 450000

21 Delta 2016-02-17 2 550000

22 Delta 2016-03-11 2 370000

1.3. Query data :

SELECT  nama,

SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,

SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,

SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,

SUM( nilai_trx ) AS total_trx

FROM tabel_sales

GROUP BY nama

Output :

1.4. Menampilkan data lengkap

SELECT  

nama,

SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,

COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,

SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,

COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,

SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,

COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,

COUNT(id_trx) AS jml_trx,

SUM( nilai_trx ) AS total_trx

FROM tabel_sales

GROUP BY nama

Output :

2. Pivot : Pivot Table Dengan MySQL Dengan WITH ROLLUP

Contoh 1 :

SELECT  IFNULL(nama, 'TOTAL') AS nama_sales,

SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,

SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,

SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,

COUNT(id_trx) AS jml_trx,

SUM( nilai_trx ) AS total_trx

FROM tabel_sales

GROUP BY nama

WITh ROLLUP

Output :

Contoh 2 :

SELECT  

IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,

IFNULL( tim, 'TOTAL' ) AS sales_tim,

 

SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,

COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,

SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,

COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,

SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,

COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,

COUNT(id_trx) AS jml_trx,

SUM( nilai_trx ) AS total_trx

FROM tabel_sales

GROUP BY tim, nama

WITH ROLLUP

Output :

Contoh 3 :

SELECT  

IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,

IFNULL( tim, 'TOTAL' ) AS sales_tim,

 

SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,

COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,

SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,

COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,

SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,

COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,

COUNT(id_trx) AS jml_trx,

SUM( nilai_trx ) AS total_trx

FROM tabel_sales

GROUP BY tim DESC, nama DESC

WITH ROLLUP

Output :

Contoh 4 :

SELECT * FROM

(

SELECT  

IFNULL( nama, '1 - SUB TOTAL' ) AS nama_sales,

IFNULL( tim, '0 - TOTAL' ) AS sales_tim,

 

SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,

COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,

SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,

COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,

SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,

COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,

COUNT(id_trx) AS jml_trx,

SUM( nilai_trx ) AS total_trx

FROM tabel_sales

GROUP BY tim,  nama

WITH ROLLUP

) AS tabel_sales

ORDER BY sales_tim DESC, nama_sales DESC

Output :

Contoh 5 :

SELECT * FROM (

SELECT  

nama,

tim,

 

SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,

COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,

SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,

COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,

SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,

COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,

COUNT(id_trx) AS jml_trx,

SUM( nilai_trx ) AS total_trx

FROM tabel_sales

GROUP BY tim,  nama

ORDER BY total_trx DESC

) AS tabel_sales

UNION ALL

SELECT 'TOTAL' AS nama, '' AS tim, 

SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,

COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,

SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,

COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,

SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,

COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,

COUNT(id_trx) AS jml_trx,

SUM( nilai_trx ) AS total_trx

FROM tabel_sales

Output :

Penggunaan Pivot Pada Oracle

 

Berikut adalah cara penggunaan Pivot pada Oracle

Contoh 1 :

select cust_id, state_code, times_purchased

from customers

order by cust_id;

Output :

Contoh 2 : Group

select state_code, times_purchased, count(1) cnt

from customers

group by state_code, times_purchased;

Output :

Contoh 3 : Pivot

select * from (

   select times_purchased, state_code

   from customers t

)

pivot 

(

   count(state_code)

   for state_code in ('NY','CT','NJ','FL','MO')

)

order by times_purchased

/

Output :

Contoh 5 : Pivot 

select * from (

   select times_purchased as "Puchase Frequency", state_code

   from customers t

)

pivot 

(

   count(state_code)

   for state_code in ('NY' as "New York",'CT' "Connecticut",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")

)

order by 1

/

Output :

Penggunaan CTE ( Common Table Expression ) Pada Oracle

Berikut adalah penggunaan CTE ( Common Table Expression ) Pada Oracle

1. Buat table 

create table STUDENT_MARKS

(

  STUDENT_ID   NUMBER,

  STUDENT_NAME VARCHAR2(100),

  TELUGU       NUMBER(3),

  HINDI        NUMBER(3),

  ENGLISH      NUMBER(3),

  MATHS        NUMBER(3),

  PHYS         NUMBER(3),

  CHEM         NUMBER(3),

  MARKS        NUMBER default "TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM",

  PERCENTAGE   NUMBER default ("TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM")*100/600

);

2. Isikan data

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)

values (1, 'VISWANATH', 87, 84, 74, 97, 74, 74);

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)

values (2, 'VINITH', 88, 83, 79, 100, 79, 89);

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)

values (3, 'RAVI', 87, 52, 59, 100, 98, 68);

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)

values (4, 'RAGHU', 88, 79, 85, 89, 69, 96);

3. Tampilkan Data

SELECT *

FROM STUDENT_MARKS;

Output :

STUDENT_ID STUDENT_NAME TELUGU HINDI ENGLISH MATHS PHYS CHEM MARKS PERCENTAGE

1         VISWANATH     87 84 74 97 74 74 490 81.6666666666667

2         VINITH     88 83 79 100 79 89 518 86.3333333333333

3         RAVI     87 52 59 100 98 68 464 77.3333333333333

4         RAGHU     88 79 85 89 69 96 506 84.3333333333333

4. Query CTE ( Common Table Expression )

WITH CTE AS

(

  SELECT STUDENT_ID,

         STUDENT_NAME,

         SUBJECT_NAME, 

         SUB_MARKS

  FROM   STUDENT_MARKS 

  UNPIVOT

         (SUB_MARKS -- unpivot_clause

                    FOR SUBJECT_NAME --  unpivot_for_clause

                    IN( -- unpivot_in_clause

                         TELUGU AS 'TELUGU',

                         HINDI AS 'HINDI',

                         ENGLISH AS 'ENGLISH',

                         MATHS AS 'MATHS',

                         PHYS AS 'PHYSICS',

                         CHEM AS 'CHEMISTRY'

                       )

          )

)

SELECT   X.STUDENT_ID,

         X.STUDENT_NAME,

         X.SUBJECT_NAME, 

         X.SUB_MARKS

FROM CTE X,

(          

   SELECT SUBJECT_NAME, 

          MAX(SUB_MARKS) SUB_MARKS 

   FROM CTE  

   GROUP BY SUBJECT_NAME

 ) Y

WHERE X.SUBJECT_NAME= Y.SUBJECT_NAME

AND X.SUB_MARKS=Y.SUB_MARKS

ORDER BY   X.SUBJECT_NAME; 

Output :

Penggunaan UnPivot Pada Oracle

Berikut adalah penggunaan UnPivot pada Oracle :

Format :

SELECT 

    select_column_list

FROM table_name

UNPIVOT [INCLUDE | EXCLUDE NULLS](

    unpivot_clause

    unpivot_for_clause

    unpivot_in_clause 

);

1. Buat table 

create table STUDENT_MARKS

(

  STUDENT_ID   NUMBER,

  STUDENT_NAME VARCHAR2(100),

  TELUGU       NUMBER(3),

  HINDI        NUMBER(3),

  ENGLISH      NUMBER(3),

  MATHS        NUMBER(3),

  PHYS         NUMBER(3),

  CHEM         NUMBER(3),

  MARKS        NUMBER default "TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM",

  PERCENTAGE   NUMBER default ("TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM")*100/600

);

2. Isikan data

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)

values (1, 'VISWANATH', 87, 84, 74, 97, 74, 74);

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)

values (2, 'VINITH', 88, 83, 79, 100, 79, 89);

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)

values (3, 'RAVI', 87, 52, 59, 100, 98, 68);

insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)

values (4, 'RAGHU', 88, 79, 85, 89, 69, 96);

3. Tampilkan Data

SELECT *

FROM STUDENT_MARKS;

Output :

STUDENT_ID STUDENT_NAME TELUGU HINDI ENGLISH MATHS PHYS CHEM MARKS PERCENTAGE

1         VISWANATH     87 84 74 97 74 74 490 81.6666666666667

2         VINITH     88 83 79 100 79 89 518 86.3333333333333

3         RAVI     87 52 59 100 98 68 464 77.3333333333333

4         RAGHU     88 79 85 89 69 96 506 84.3333333333333

4. Query UnPivot

SELECT STUDENT_ID,

         STUDENT_NAME,

         SUBJECT_NAME, 

         SUB_MARKS

  FROM   STUDENT_MARKS 

  UNPIVOT

         (SUB_MARKS -- unpivot_clause

                    FOR SUBJECT_NAME --  unpivot_for_clause

                    IN( -- unpivot_in_clause

                         TELUGU AS 'TELUGU',

                         HINDI AS 'HINDI',

                         ENGLISH AS 'ENGLISH',

                         MATHS AS 'MATHS',

                         PHYS AS 'PHYSICS',

                         CHEM AS 'CHEMISTRY'

                       )

          );

Output :

Penggunaan CTE ( Common Table Expression ) Pada SQL Server

 

Common Table Expression (CTE) merupakan salah satu bentuk query SQL yang digunakan untuk menyederhanakan JOIN pada SQL kedalam subqueries dan mampu memberikan query yang bersifat hieararki. CTE dikenal dengan istilah Hierarchical and recursive queries in SQL.

Berikut adalah penggunaan CTE ( Common Table Expression ) pada SQL Server :

Format :

WITH expression_name[(column_name [,...])] AS (CTE_definition) SQL_statement;

Contoh 1 :

;with ROWCTE(ROWNO) as ( SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO FROM sys.databases WHERE database_id <= 10 ) SELECT * FROM ROWCTE

Hasil :

Contoh 2 :

WITH cte_sales_amounts (staff, sales, year) AS (

    SELECT    

        first_name + ' ' + last_name, 

        SUM(quantity * list_price * (1 - discount)),

        YEAR(order_date)

    FROM    

        sales.orders o

    INNER JOIN sales.order_items i ON i.order_id = o.order_id

    INNER JOIN sales.staffs s ON s.staff_id = o.staff_id

    GROUP BY 

        first_name + ' ' + last_name,

        year(order_date)

)

SELECT

    staff, 

    sales

FROM 

    cte_sales_amounts

WHERE

    year = 2018;

Hasil :

Contoh 3 :

WITH cte_sales AS ( SELECT staff_id, COUNT(*) order_count FROM sales.orders WHERE YEAR(order_date) = 2018 GROUP BY staff_id ) SELECT AVG(order_count) average_orders_by_staff FROM cte_sales;

Hasil :

average_orders_by_staff ----------------------- 48 (1 row affected)

Contoh 4 :

WITH cte_category_counts (

    category_id, 

    category_name, 

    product_count

)

AS (

    SELECT 

        c.category_id, 

        c.category_name, 

        COUNT(p.product_id)

    FROM 

        production.products p

        INNER JOIN production.categories c 

            ON c.category_id = p.category_id

    GROUP BY 

        c.category_id, 

        c.category_name

),

cte_category_sales(category_id, sales) AS (

    SELECT    

        p.category_id, 

        SUM(i.quantity * i.list_price * (1 - i.discount))

    FROM    

        sales.order_items i

        INNER JOIN production.products p 

            ON p.product_id = i.product_id

        INNER JOIN sales.orders o 

            ON o.order_id = i.order_id

    WHERE order_status = 4 -- completed

    GROUP BY 

        p.category_id

) 

SELECT 

    c.category_id, 

    c.category_name, 

    c.product_count, 

    s.sales

FROM

    cte_category_counts c

    INNER JOIN cte_category_sales s 

        ON s.category_id = c.category_id

ORDER BY 

    c.category_name;

Hasil :

Penggunaan UnPivot Pada SQL Server

 

Berikut adalah penggunaan UnPivot pada SQL Server :

Contoh 1 :

-- Create the table and insert values as portrayed in the previous example.  

CREATE TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT,  

    Emp3 INT, Emp4 INT, Emp5 INT);  

GO  

INSERT INTO pvt VALUES (1,4,3,5,4,4);  

INSERT INTO pvt VALUES (2,4,1,5,5,5);  

INSERT INTO pvt VALUES (3,4,3,5,4,4);  

INSERT INTO pvt VALUES (4,4,2,5,5,4);  

INSERT INTO pvt VALUES (5,5,1,5,5,5);  

GO  

-- Unpivot the table.  

SELECT VendorID, Employee, Orders  

FROM   

   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5  

   FROM pvt) p  

UNPIVOT  

   (Orders FOR Employee IN   

      (Emp1, Emp2, Emp3, Emp4, Emp5)  

)AS unpvt;  

GO

Hasil :

VendorID    Employee    Orders

----------- ----------- ------

1            Emp1       4

1            Emp2       3 

1            Emp3       5

1            Emp4       4

1            Emp5       4

2            Emp1       4

2            Emp2       1

2            Emp3       5

2            Emp4       5

2            Emp5       5

...

Contoh 2 :

SELECT territory, sales

FROM

(

  SELECT 

      [Europe]

     ,[North America]

     ,[Pacific]

 FROM [dbo].[salesterritoryPivot]

) p

UNPIVOT

(

  sales for territory IN

  ([Europe],[North America],[Pacific])

) AS upvt;

Hasil :

Penggunaan Pivot Pada SQL Server

 

Berikut adalah cara penggunaan Pivot pada SQL Server :

Format :

SELECT <non-pivoted column>,  

    [first pivoted column] AS <column name>,  

    [second pivoted column] AS <column name>,  

    ...  

    [last pivoted column] AS <column name>  

FROM  

    (<SELECT query that produces the data>)   

    AS <alias for the source query>  

PIVOT  

(  

    <aggregation function>(<column being aggregated>)  

FOR   

[<column that contains the values that will become column headers>]   

    IN ( [first pivoted column], [second pivoted column],  

    ... [last pivoted column])  

) AS <alias for the pivot table>  

<optional ORDER BY clause>;

1. Basic PIVOT Example

Contoh 1 :

Query :

USE AdventureWorks2014 ;  

GO  

SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost   

FROM Production.Product  

GROUP BY DaysToManufacture;

Hasil :

DaysToManufacture AverageCost

----------------- -----------

0                 5.0885

1                 223.88

2                 359.1082

4                 949.4105

Query Pivot :

-- Pivot table with one row and five columns  

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,   

  [0], [1], [2], [3], [4]  

FROM  

(

  SELECT DaysToManufacture, StandardCost   

  FROM Production.Product

) AS SourceTable  

PIVOT  

(  

  AVG(StandardCost)  

  FOR DaysToManufacture IN ([0], [1], [2], [3], [4])  

) AS PivotTable;

Hasil :

Cost_Sorted_By_Production_Days 0           1           2           3           4         

------------------------------ ----------- ----------- ----------- ----------- -----------

AverageCost                    5.0885      223.88      359.1082    NULL        949.4105

Contoh 2 :

Query tanpa Pivot :

SELECT 

  [Group],

  SUM([SalesYTD]) SalesYTD

FROM [Sales].[SalesTerritory]

GROUP BY [Group]

Hasil :

Query Pivot :

SELECT 'SalesYTD' AS SalesYTD, [Europe], [North America], [Pacific]

FROM  

(

  SELECT SalesYTD, [Group]   

  FROM [Sales].[SalesTerritory]

) AS TableToPivot 

PIVOT  

(  

  SUM(SalesYTD)  

  FOR [Group] IN ([Europe], [North America], [Pacific])  

) AS PivotTable; 

Hasil :

2. Query Pivot Complex

USE AdventureWorks2014;  

GO  

SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5  

FROM   

(SELECT PurchaseOrderID, EmployeeID, VendorID  

FROM Purchasing.PurchaseOrderHeader) p  

PIVOT  

(  

COUNT (PurchaseOrderID)  

FOR EmployeeID IN  

( [250], [251], [256], [257], [260] )  

) AS pvt  

ORDER BY pvt.VendorID;

Hasil :

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5  

----------- ----------- ----------- ----------- ----------- -----------

1492                   2           5           4           4           4

1494                   2           5           4           5           4

1496                   2           4           4           5           5

1498                   2           5           4           4           4

1500                   3           4           4           5           4

Contoh 1.2 :

1.2.1. Query tanpa Pivot

SELECT YEAR(SOH.OrderDate) AS SalesYear, 

       MONTH(SOH.OrderDate) AS SalesMonth, 

       SUM(SOH.SubTotal) AS TotalSales

FROM sales.SalesOrderHeader SOH

     JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId

GROUP BY YEAR(SOH.OrderDate), 

         MONTH(SOH.OrderDate)

ORDER BY YEAR(SOH.OrderDate), 

         MONTH(SOH.OrderDate);

Hasil :

1.2.2. Query Pivot

Contoh 1 :

SELECT SalesYear, 

       ISNULL([Q1], 0) AS Q1, 

       ISNULL([Q2], 0) AS Q2, 

       ISNULL([Q3], 0) AS Q3, 

       ISNULL([Q4], 0) AS Q4, 

       (ISNULL([Q1], 0) + ISNULL([Q2], 0) + ISNULL([Q3], 0) + ISNULL([Q4], 0)) SalesYTD

FROM

(

    SELECT YEAR(SOH.OrderDate) AS SalesYear, 

           CAST('Q'+CAST(DATEPART(QUARTER, SOH.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)) Quarters, 

           SOH.SubTotal AS TotalSales

    FROM sales.SalesOrderHeader SOH

         JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId

 ) AS Data PIVOT(SUM(TotalSales) FOR Quarters IN([Q1], 

                                                [Q2], 

                                                [Q3], 

                                                [Q4])) AS pvt

ORDER BY SalesYear;

Hasil :

Contoh 2 :

SELECT SalesYear, 

       ISNULL([1], 0) AS Jan, 

       ISNULL([2], 0) AS Feb, 

       ISNULL([3], 0) AS Mar, 

       ISNULL([4], 0) AS Apr, 

       ISNULL([5], 0) AS May, 

       ISNULL([6], 0) AS Jun, 

       ISNULL([7], 0) AS Jul, 

       ISNULL([8], 0) AS Aug, 

       ISNULL([9], 0) AS Sep, 

       ISNULL([10], 0) AS Oct, 

       ISNULL([11], 0) AS Nov, 

       ISNULL([12], 0) AS Dec, 

       (ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0) + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0) + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0)) SalesYTD

FROM

(

    SELECT YEAR(SOH.OrderDate) AS SalesYear, 

           DATEPART(MONTH, SOH.OrderDate) Months,

          SOH.SubTotal AS TotalSales

    FROM sales.SalesOrderHeader SOH

         JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId

 ) AS Data PIVOT(SUM(TotalSales) FOR Months IN([1], 

                                                  [2], 

                                                  [3], 

                                                  [4], 

                                                  [5], 

                                                  [6], 

                                                  [7], 

                                                  [8], 

                                                  [9], 

                                                  [10], 

                                                  [11], 

                                                  [12])) AS pvt;

Hasil :

Contoh 3 :

DECLARE @dml AS NVARCHAR(MAX)

DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 

       + QUOTENAME(Months)

FROM (SELECT DISTINCT  DATEPART(MONTH, SOH.OrderDate) Months

         FROM sales.SalesOrderHeader SOH

         JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId

    GROUP BY YEAR(SOH.OrderDate),

DATEPART(MONTH, SOH.OrderDate)) AS Months

 

 --Prepare the PIVOT query using the dynamic 

SET @dml = 

  N'SELECT SalesYear, ' +@ColumnName + ' FROM

  (

  SELECT YEAR(SOH.OrderDate) AS SalesYear, 

           DATEPART(MONTH, SOH.OrderDate) Months,

           SUM(SOH.SubTotal) AS TotalSales

   FROM sales.SalesOrderHeader SOH

         JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId

    GROUP BY YEAR(SOH.OrderDate),

    DATEPART(MONTH, SOH.OrderDate)) AS T

    PIVOT(SUM(TotalSales) 

           FOR Months IN (' + @ColumnName + ')) AS PVTTable'

--Print @DynamicPivotQuery

--Execute the Dynamic Pivot Query

EXEC sp_executesql @dml

Hasil :