我知道这听起来可能很傻,但我想创建一个函数来处理来自不同大小表的数据。
假设我有第一张这样的桌子:
ID IRR M0 M1
----------------------
1 0 -10 5
2 0 -20 10
3 0 -100 100
4 0 -10 0
第二张表是这样的:
ID IRR M0 M1 M2
----------------------------
1 0 -10 5 60
2 0 -20 10 0
3 0 -100 100 400
4 0 -10 0 10
我想创建一个能够处理两个表中数据的函数。
我知道第一列包含ID,第二列包含IRR,其余列将包含特定月份的现金流。
函数应该能够处理所有列而不是前2列,并将结果存储在第二列中。
我知道我可以用从特定的表中获取所有列
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'First_Table'
当我想创建将这些列作为行返回的函数时,问题就开始了。
我可以创建这样的函数:
CREATE FUNCTION UnpivotRow (@TableName varchar(50), @FromWhichColumn int, @Row int)
RETURNS @values TABLE
(
id INT IDENTITY(0, 1),
value DECIMAL(30, 10)
)
...
但是这个函数应该是什么样子呢?
我认为这种处理表的理想外观应该是这样的:
ProjectID TimePeriod Value
--------------------------------
1 0 -10
1 1 5
2 0 -20
2 1 10
3 0 -100
3 1 100
4 0 -10
4 1 0
我需要在不知道列数的情况下取消透视整个表
编辑:如果这不能在函数内部完成,那么可能在过程内部完成?
这可以使用动态SQL来执行UNPIVOT
:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colTP as NVARCHAR(MAX)
select @cols = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('table1') and
C.name like 'M%'
for xml path('')), 1, 1, '')
set @query = 'SELECT id,
replace(timeperiod, ''M'', '''') timeperiod,
value
from table1
unpivot
(
value
for timeperiod in (' + @cols + ')
) u '
exec(@query)
请参阅SQL Fiddle with Demo。
此解决方案必须放置在存储过程中。