假设数据反映了这个简单的示例-您可以使用动态SQL查询来创建Unpivot（）语句。给定示例数据：

CREATE TABLE myTable ([Product] nvarchar(50), [01.01.2001] int, [02.01.2021] int);
INSERT INTO myTable VALUES (N'product 1', 10, 11), (N'product 2', 11, 12);

这个简单示例的unpivot看起来像这样：

SELECT unpvt.[Product], unpvt.[Date], unpvt.[Quantity] 
  FROM (SELECT  [Product], [01.01.2001], [02.01.2021] 
  FROM [myTable]) AS p 
  UNPIVOT ([Quantity] FOR [Date] IN ( [01.01.2001], [02.01.2021])) AS unpvt
  ORDER BY unpvt.[Product], unpvt.[Date];

可以生成上面的unpivot代码的动态字符串看起来像这样：

--Declare your tableName, unGrouping column and new column names:
DECLARE @tableName nvarchar(100) = N'myTable'
DECLARE @unGroupCol nvarchar(100) = N'Product'
DECLARE @newUnPivColsName nvarchar(100) = N'Date'
DECLARE @newUnAggColsName nvarchar(100) = N'Quantity'

DECLARE @sql nvarchar(max) 
 = CONCAT(N'
SELECT unpvt.[', @unGroupCol, '], [', @newUnPivColsName, '] = CAST(unpvt.[', @newUnPivColsName, '] as date), unpvt.[', @newUnAggColsName, 

  '] FROM (SELECT '
 , STUFF((SELECT CONCAT(', [', COLUMN_NAME, ']') 
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = @tableName
       AND (COLUMN_NAME = @unGroupCol  --the Product column in this case
            OR
            COLUMN_NAME LIKE '__.__.____' --the format of the various date columns
           )
         
    --ORDER BY ORDINAL_POSITION  
    FOR XML PATH ('')   
    ), 1, 1, '') 

 ,        ' FROM [', @tableName,  
       ']) AS p UNPIVOT ([' , @newUnAggColsName,'] FOR [', @newUnPivColsName, '] IN ('

 , STUFF((SELECT CONCAT(', [', COLUMN_NAME, ']') 
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = @tableName
       AND COLUMN_NAME LIKE '__.__.____' --the format of the various date columns
       AND COLUMN_NAME <> @unGroupCol --exclude the [Product] column
    --ORDER BY ORDINAL_POSITION  
    FOR XML PATH ('')   
    ), 1, 1, '') 
 , ')) AS unpvt ORDER BY [', @unGroupCol, '], [', @newUnPivColsName, '];')

EXEC (@sql);

这需要指定表名、取消分组列、新的取消透视列名和新的解聚列名。
从INFORMATION_SCHEMA_COLUMNS获取原始源数据的列名。在动态代码中，我还将原始日期列名称转换为日期数据类型。源日期列是使用正则表达式模式'__.__.____'标识的，因为它反映了样本数据。

您可以使用PowerQuery来取消透视数据：

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Unpivot" = Table.UnpivotOtherColumns(Source, {"Product"}, "Date", "Quantity")
in
    #"Unpivot"

您必须将“Table1”替换为您的表名。