sql - 如何将列名动态传递给 SQL 中的 unpivot 函数？

Question

我们有一个列名列表，每个月都会不断添加。我希望下面的代码动态捕获列名（在 unpivot 函数中）：

DROP TABLE IF EXISTS [Commodity-Wise].[dbo].[temp]
SELECT * INTO [Commodity-Wise].[dbo].[temp]
FROM
(SELECT [HS_CODE]
      ,[DESCRIPTION]
      ,[TYPE]
      ,[CURRENCY]
      ,[FINANCIAL YEAR]
      ,[VALUE]
FROM [Commodity-Wise].[dbo].[Import_Export_INR_USD_15_19]
UNPIVOT
(
    [VALUE] for [FINANCIAL YEAR] IN 
(APR-15 Value
      ,MAY-15 Value
      ,JUN-15 Value
      ,JUL-15 Value)
)a)b

我试过复制下面的代码：

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Import_Export_INR_USD_15_19'
AND COLUMN_NAME LIKE '%Value%'

在上面的“UNPIVOT”函数中，但它不起作用。有什么建议么？

标签： sqlsql-server

Answer 1

请尝试以下内容

use [AdventureWorks2019]
go

CREATE TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT,  
    Emp3 INT, Emp4 INT, Emp5 INT, Emp6 INT);  
GO  
INSERT INTO pvt VALUES (1,4,3,5,4,4,5);  
INSERT INTO pvt VALUES (2,4,1,5,5,5,7);  
INSERT INTO pvt VALUES (3,4,3,5,4,4,9);  
INSERT INTO pvt VALUES (4,4,2,5,5,4,0);  
INSERT INTO pvt VALUES (5,5,1,5,5,5,1);  
GO  


Declare @Columns VARCHAR(100)='', @Dynamic VARCHAR(MAX)=''
SELECT @Columns = @Columns + ',' + [Name] FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('[dbo].[pvt]')
AND [Name] Like 'Emp%'
SET @Columns = STUFF(@Columns,1,1,'')

SET @Dynamic ='
SELECT VendorID, Employee, Orders  
FROM   
   (SELECT VendorID, ' + @Columns + ' 
   FROM pvt) p  
UNPIVOT  
   (Orders FOR Employee IN   
      (' + @Columns +')  
)AS unpvt;'  

EXEC(@Dynamic)

来源：https ://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15