从分表中按列名排序表

Question

我有两个单独的表，一个是事务表，另一个是包含要生成的报告列表的表。包含报告的表有需要满足的条件和排序顺序。

Transaction

Emp_ID  Amount  Date

1       100     2020-01-01
2       90      2020-01-04
3       85      2020-01-02
4       40      2020-01-07
5       60      2020-01-08

ReportMaster

ReportID    Sorting     Asc_Desc
1           Amount      Desc
2           Date        Asc
3           Amount      Asc

最终报告将显示联盟中每个报告的前2个结果。

ReportID    Emp_ID  Amount  Date        Row_Number
1           1       100     2020-01-01  1
1           2       90      2020-01-04  2
2           1       100     2020-01-01  1
2           3       85      2020-01-02  2
3           4       40      2020-01-07  1
3           5       60      2020-01-08  2

我对此的逻辑已经走到了死胡同。使用带有“排序”值的order子句，它将只返回Amount/Department，而不是相应列中的值。请记住，实际数据集包含20多列和50-60个报告，因此按顺序编写case语句有点单调乏味。

是否需要递归语句？或者，有没有其他方法来实现这一点？

Answer 1

尝试下面的sql：

DECLARE @sql NVARCHAR(MAX) = ''
IF OBJECT_ID('tempdb..#ReportTypes') IS NOT NULL DROP TABLE #ReportTypes
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (
ReportID INT,
Emp_ID INT,
Amount DECIMAL(18, 2),
Date DATETIME,
RowNumber INT
)

SELECT ReportId, Sorting, Asc_Desc
INTO #ReportTypes
FROM ReportMaster

WHILE (SELECT COUNT(*) FROM #ReportTypes) > 0
BEGIN
    DECLARE @ReportId INT, @Sorting VARCHAR(50), @Asc_Desc VARCHAR(50)
    SELECT TOP 1 @ReportID = ReportId, @Sorting = Sorting, @Asc_Desc = Asc_Desc FROM #ReportTypes

    SET @sql = @sql + ' INSERT INTO #Results ' 
            + ' SELECT ' + CAST(@ReportId AS VARCHAR) + ' ,Emp_ID, Amount, Date, ROW_NUMBER() OVER(ORDER BY ' + @Sorting + ' ' + @Asc_Desc + ') AS RowNumber FROM [Transactions];'

    DELETE FROM #ReportTypes WHERE ReportId = @ReportId 
END

EXECUTE sp_executesql @sql

SELECT ReportID, Emp_ID, Amount, Date, RowNumber AS [Row_Number]
FROM #Results
WHERE RowNumber <= 2
ORDER BY ReportID, RowNumber