Etiket arşivi: table

Fuat Tatar 11:19 on 20 June 2012
Etiketler: mssql, pivot, table   

Mssql pivot table kullanımı.

Karşılaştığım sorgularda fark ettiğim olay pivot kullanılmaması. Örnek olması açısından paylaşmak istedim. Aşağıda aynı sonucu veren 2 tane query var biri pivot kullanılarak biride kullanılmayarak hazırlanmış. Sonuçlar aynı tatbikî. Ama pivot kullanmadan aşağıdaki sonucu almak için iki kat fazla sorgu yazılmış. Geri dönüp bakıldığında anlaşılması çok zorlaşıyor.  Örnekte adventurework veri tabanı kullandım.
Sonuç:
 
Pivot Kullanılmadan
SELECT Table1.ShiftName
      ,SUM(Table1.Production) AS Production
      ,SUM(Table1.Engineering) AS Engineering
      ,SUM(Table1.Marketing) AS Marketing
FROM (
      SELECT S.NAME AS ShiftName
            ,COUNT(H.BusinessEntityID) AS Production
            ,0 ASEngineering
            ,0 AS Marketing
      FROM HumanResources.EmployeeDepartmentHistory H
      INNER JOINHumanResources.Department D
            ON H.DepartmentID = D.DepartmentID
      INNER JOINHumanResources.Shift S
            ON H.ShiftID = S.ShiftID
      WHERE H.EndDate IS NULL
            AND D.NAME = ‘Production’
      GROUP BY S.NAME
     
      UNION ALL
     
      SELECT S.NAME AS ShiftName
            ,0 ASProduction
            ,COUNT(H.BusinessEntityID) AS Engineering
            ,0 AS Marketing
      FROM HumanResources.EmployeeDepartmentHistory H
      INNER JOINHumanResources.Department D
            ON H.DepartmentID = D.DepartmentID
      INNER JOINHumanResources.Shift S
            ON H.ShiftID = S.ShiftID
      WHERE H.EndDate IS NULL
            AND D.NAME = ‘Engineering’
      GROUP BY S.NAME
     
      UNION ALL
     
      SELECT S.NAME AS ShiftName
            ,0 ASProduction
            ,0 ASEngineering
            ,COUNT(H.BusinessEntityID) AS Marketing
      FROM HumanResources.EmployeeDepartmentHistory H
      INNER JOINHumanResources.Department D
            ON H.DepartmentID = D.DepartmentID
      INNER JOINHumanResources.Shift S
            ON H.ShiftID = S.ShiftID
      WHERE H.EndDate IS NULL
            AND D.NAME = ‘Marketing’
      GROUP BY S.NAME
      ) Table1
GROUP BY Table1.ShiftName
Pivot kullanılarak
SELECT * FROM (SELECT S.NAME AS ShiftName
      ,H.BusinessEntityID
      ,D.NAME DepartmentName
FROM HumanResources.EmployeeDepartmentHistory H
INNER JOIN HumanResources.Department D
      ON H.DepartmentID = D.DepartmentID
INNER JOIN HumanResources.Shift S
      ON H.ShiftID = S.ShiftID
WHERE H.EndDate IS NULL
      AND D.NAME IN (
            ‘Production’
            ,‘Engineering’
            ,‘Marketing’
            )
)AS a
PIVOT
(
COUNT(BusinessEntityID)
FOR DepartmentName IN ([Production],[Engineering],[Marketing])
)AS b