• TSQL Pivot Tables(行列转换) in SQL Server 2005/2008


    T-SQL Pivot Syntax

    SELECT

      [non-pivoted column], -- optional

      [additional non-pivoted columns], -- optional

      [first pivoted column],

      [additional pivoted columns]

    FROM (

      SELECT query producing sql data for pivot

      -- select pivot columns as dimensions and

      -- value columns as measures from sql tables

    ) AS TableAlias

    PIVOT

    (

      <aggregation function>(column for aggregation or measure column) -- MIN,MAX,SUM,etc

      FOR [<column name containing values for pivot table columns>]

      IN (

        [first pivoted column], ..., [last pivoted column]

      )

    ) AS PivotTableAlias

    ORDER BY clause -- optional

    T-SQL Pivot Table Examples in AdventureWorks SQL Server sample database

    select

      PS.Name, P.Color, PIn.Quantity

    from Production.Product P

    inner join Production.ProductSubcategory PS

      on PS.ProductSubcategoryID = P.ProductSubcategoryID

    left join Production.ProductInventory PIn

      on P.ProductID = PIn.ProductID

    代码
      1 use AdventureWorks
      2 go
      3 ---geovindu@163.com  涂聚文
      4 select
      5   *
      6 from
      7 (
      8   select
      9     PS.Name, P.Color, PIn.Quantity
     10   from Production.Product P
     11   inner join Production.ProductSubcategory PS
     12     on PS.ProductSubcategoryID = P.ProductSubcategoryID
     13   left join Production.ProductInventory PIn
     14     on P.ProductID = PIn.ProductID
     15 ) DataTable
     16 PIVOT
     17 (
     18 --ISNULL(NULLIF(d,0)
     19   SUM(Quantity))
     20   FOR Color
     21   IN (
     22     [Black],[Blue],[Grey],[Multi],[Red],
     23     [Silver],[Silver/Black],[White],[Yellow]
     24   )
     25 ) PivotTable
     26 
     27 --
     28 SELECT *
     29 FROM (
     30   SELECT
     31     YEAR(OrderDate) [Year],
     32     MONTH(OrderDate) [Month],
     33     SubTotal
     34   FROM Sales.SalesOrderHeader
     35 ) TableDate
     36 PIVOT (
     37   SUM(SubTotal)
     38   FOR [Month] IN (
     39     [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
     40   )
     41 ) PivotTable
     42 
     43 --
     44 SELECT *
     45 FROM (
     46   SELECT
     47     YEAR(OrderDate) [Year],
     48     CASE MONTH(OrderDate)
     49       WHEN 1 THEN 'January'
     50       WHEN 2 THEN 'February'
     51       WHEN 3 THEN 'March'
     52       WHEN 4 THEN 'April'
     53       WHEN 5 THEN 'May'
     54       WHEN 6 THEN 'June'
     55       WHEN 7 THEN 'July'
     56       WHEN 8 THEN 'August'
     57       WHEN 8 THEN 'September'
     58       WHEN 8 THEN 'October'
     59       WHEN 8 THEN 'November'
     60       WHEN 8 THEN 'December'
     61     END as [Month],
     62     SubTotal
     63   FROM Sales.SalesOrderHeader
     64 ) TableDate
     65 PIVOT (
     66   SUM(SubTotal)
     67   FOR [Month] IN (
     68     [January],[February],[March],[April],
     69     [May],[June],[July],[August],
     70     [September],[October],[November],[December]
     71   )
     72 ) PivotTable
     73 
     74 ---
     75 
     76 SELECT *
     77 FROM(
     78   SELECT
     79     YEAR(DueDate) [Year],
     80     CASE MONTH(DueDate)
     81       WHEN 1 THEN 'January'
     82       WHEN 2 THEN 'February'
     83       WHEN 3 THEN 'March'
     84       WHEN 4 THEN 'April'
     85       WHEN 5 THEN 'May'
     86       WHEN 6 THEN 'June'
     87       WHEN 7 THEN 'July'
     88       WHEN 8 THEN 'August'
     89       WHEN 9 THEN 'September'
     90       WHEN 10 THEN 'October'
     91       WHEN 11 THEN 'November'
     92       WHEN 12 THEN 'December'
     93     END as [Month],
     94     ProductID,
     95     OrderQty
     96   FROM Production.WorkOrder
     97 ) WorkOrders
     98 PIVOT
     99 (
    100   SUM(OrderQty)
    101   FOR [Month] IN (
    102     [January],[February],[March],[April],
    103     [May],[June],[July],[August],
    104     [September],[October],[November],[December]
    105   )
    106 AS PivotTable
    107 ORDER BY [Year], ProductID
    108 
    109 ---
    110 DECLARE @PivotColumnHeaders VARCHAR(MAX)
    111 SELECT @PivotColumnHeaders =
    112   COALESCE(
    113     @PivotColumnHeaders + ',[' + cast(Name as varchar+ ']',
    114     '[' + cast(Name as varchar)+ ']'
    115   )
    116 FROM Sales.SalesTerritory
    117 
    118 DECLARE @PivotTableSQL NVARCHAR(MAX)
    119 SET @PivotTableSQL = N'
    120   SELECT *
    121   FROM (
    122     SELECT
    123       YEAR(H.OrderDate) [Year],
    124       T.Name,
    125       H.TotalDue
    126     FROM Sales.SalesOrderHeader H
    127     LEFT JOIN Sales.SalesTerritory T
    128       ON H.TerritoryID = T.TerritoryID
    129   ) AS PivotData
    130   PIVOT (
    131     SUM(TotalDue)
    132     FOR Name IN (
    133       ' + @PivotColumnHeaders + '
    134     )
    135   ) AS PivotTable
    136 '
    137 
    138 EXECUTE(@PivotTableSQL)
  • 相关阅读:
    UVa 1331 最大面积最小的三角剖分
    UVa 1626 括号序列(矩阵连乘)
    POJ 3295 Tautology(构造法)
    POJ 2586 Y2K Accounting Bug(贪心)
    POJ 2109 Power of Cryptography
    abcd
    好数
    Gift
    密码游戏
    约瑟夫游戏
  • 原文地址:https://www.cnblogs.com/geovindu/p/1713366.html
Copyright © 2020-2023  润新知