SQLServer - 同一列上的多個 PIVOT

我有一個表格數據，如下所示，從 1 月到 12 月，類型為正常/不規則。

我需要一個查詢來執行下表所示的數據：1 月，下面分為兩個 jRegular 和 jIrregular。使用 PIVOT 或交叉表的全年數據。

   CREATE TABLE [dbo].[Details](   
       [ID] [int] IDENTITY(1,1) NOT NULL,  
       [Type] [nvarchar](10) NOT NULL,     
       [Header] [nvarchar](50) NOT NULL,   
       [SubHeader] [nvarchar](50) NOT NULL,    
       [January] [decimal](18, 2) NOT NULL,    
       [February] [decimal](18, 0) NOT NULL,   
       [March] [decimal](18, 2) NOT NULL,  
       [April] [decimal](18, 2) NOT NULL,  
       [May] [decimal](18, 2) NOT NULL,    
       [June] [decimal](18, 2) NOT NULL,   
       [July] [decimal](18, 2) NOT NULL ) ON [PRIMARY]
       
       GO SET IDENTITY_INSERT [dbo].[Details] ON 
       
       GO INSERT [dbo].[Details] ([ID], [Type], [Header], [SubHeader], 
   [January], [February], [March], [April], [May], [June], [July]) 
   VALUES (2, N'Regular', N'Item-1', N'A.Sub-Item1', 
           CAST(64.92 AS Decimal(18, 2)), CAST(57 AS Decimal(18, 0)), 
           CAST(89.67 AS Decimal(18, 2)), 
   CAST(99.20 AS Decimal(18, 2)), CAST(76.34 AS Decimal(18, 2)), 
   CAST(23.33 AS Decimal(18, 2)), CAST(32.23 AS Decimal(18, 2))) 
   GO 
   
   INSERT [dbo].[Details] ([ID], [Type], [Header], [SubHeader], 
   [January], [February], [March], [April], [May], [June], [July]) 
   VALUES (3, N'Irregular', N'Item-1', N'A.Sub-Item1', 
  CAST(43.45 AS Decimal(18, 2)), CAST(65 AS Decimal(18, 0)), 
  CAST(65.88 AS Decimal(18, 2)), 
   CAST(56.77 AS Decimal(18, 2)), CAST(45.65 AS Decimal(18, 2)), 
   CAST(56.87 AS Decimal(18, 2)), CAST(67.55 AS Decimal(18, 2))) 
   GO 
   
   SET IDENTITY_INSERT [dbo].[Details] OFF 
   GO

   SELECT Header, SubHeader,
     Regular, Irregular,
     FRegular, FIrregular,
     MrRegular, MrIrregular
     FROM (SELECT Header, SubHeader, Type, 
          'F'+Type AS FType, 
          'Mr'+Type as MType, 
          'Ap'+Type AType, 
          'Ma'+Type as May,
     [January], [February], [March] 
     from [CashFlow].[dbo].[Details] ) p
   
     PIVOT (MAX(January) FOR Type in (Regular, Irregular)) AS JPVT
     PIVOT (MAX([February]) FOR FType in (FRegular, FIrregular)) AS FPVT
     PIVOT (MAX(March) FOR MType in (MrRegular, MrIrregular)) AS MrPVT

任何人都可以幫助我。

按標題和子標題分組，並取如下所示的總和

SELECT Header, SubHeader,
 Sum(Regular), Sum(Irregular),
 Sum(FRegular), Sum(FIrregular),
 Sum(MrRegular), Sum(MrIrregular)
 FROM (SELECT Header, 
              SubHeader, 
              Type, 'F'+Type AS FType, 
              'Mr'+Type as MType, 
              'Ap'+Type AType, 
              'Ma'+Type as May,
              January], [February], [March] 
 from [CashFlow].[dbo].[Details] ) p

 PIVOT (MAX(January) FOR Type in (Regular, Irregular)) AS JPVT
 PIVOT (MAX([February]) FOR FType in (FRegular, FIrregular)) AS FPVT
 PIVOT (MAX(March) FOR MType in (MrRegular, MrIrregular)) AS MrPVT
Group by Header, SubHeader

您可以執行此操作的另一種方法是先對數據進行反透視，然後在單個操作中對其進行透視。請參見下面的範例。我必須做的唯一改變是二月的列與其他列不同（十進制（18,0）而不是十進制（18,2）），但在修復之後，它工作得很好。

;WITH CTE_UP AS
   (
   SELECT [Header] 
       , [SubHeader]
       , [MonthType] = LEFT([Month], 3) + [Type]
       , [Value] 
   FROM [CashFlow].[dbo].[Details]
       UNPIVOT ([Value] FOR [Month] IN (January, February, March, April, May, June, July)) AS unpvt 
   )
, CTE_P AS
   (
   SELECT [Header]
       , [SubHeader]
       , JanRegular AS Regular
       , JanIrregular AS Irregular
       , FebRegular AS FRegular
       , FebIrregular AS FIrregular
       , MarRegular AS MrRegular
       , MarIrregular AS MrIrregular
   FROM CTE_UP AS UP 
       PIVOT (SUM([Value]) FOR MonthType IN (JanRegular, JanIrregular, FebRegular, FebIrregular, MarRegular, MarIrregular)) AS pvt
   )
SELECT * FROM CTE_P 

引用自：https://dba.stackexchange.com/questions/235247