sql - 如何在 TSQL 中按动态列排序？

Question

我有这些桌子

CREATE TABLE [dbo].[Columns]
(
    [ColumnId] INT,
    [TableId] INT NOT NULL,
    [ColumnName] NVARCHAR(150) NOT NULL,
    [Order] INT,
    [Key] BIT
)

CREATE TABLE [dbo].[Tables]
(
    [TableId] INT,
    [TableName] NVARCHAR(200)
)

CREATE TABLE [dbo].[RowValues]
(
    [ColumnId] INT NOT NULL,
    [RowNumber] INT NOT NULL,
    [Value] NVARCHAR(200) NOT NULL
)

使用此示例数据：

insert into [Columns] values (1, 1, 'StudentName', 1, 1)
insert into [Columns] values (2, 1, 'Grade', 1, 0)
insert into [Columns] values (3, 1, 'Year', 1, 0)
insert into [Columns] values (4, 1, 'Section', 1, 0)

insert into [Columns] values (5, 2, 'TeacherName', 1, 1)
insert into [Columns] values (6, 2, 'Department', 1, 0)

insert into [Tables] values (1, 'Student')
insert into [Tables] values (2, 'Teacher')

insert into [RowValues] values (1, 1, 'Student Alan')
insert into [RowValues] values (2, 1, '99')
insert into [RowValues] values (3, 1, '1st')
insert into [RowValues] values (4, 1, 'Section 1')

insert into [RowValues] values (1, 2, 'Student Alex')
insert into [RowValues] values (2, 2, '98')
insert into [RowValues] values (3, 2, '1st')
insert into [RowValues] values (4, 2, 'Section 1')

insert into [RowValues] values (1, 3, 'Student Alfonso')
insert into [RowValues] values (2, 3, '97')
insert into [RowValues] values (3, 3, '1st')
insert into [RowValues] values (4, 3, 'Section 1')

insert into [RowValues] values (1, 4, 'Student Ben')
insert into [RowValues] values (2, 4, '96')
insert into [RowValues] values (3, 4, '1st')
insert into [RowValues] values (4, 4, 'Section 1')

insert into [RowValues] values (1, 5, 'Student Cathy')
insert into [RowValues] values (2, 5, '95')
insert into [RowValues] values (3, 5, '1st')
insert into [RowValues] values (4, 5, 'Section 1')

insert into [RowValues] values (5, 1, 'Teacher Tesso')
insert into [RowValues] values (6, 1, 'Biology Dept')

insert into [RowValues] values (5, 2, 'Teacher Marvin')
insert into [RowValues] values (6, 2, 'Math Dept')

我有这个存储过程：

CREATE PROCEDURE [dbo].[usp_DynamicSearch_Paged]
    (@searchTerm NVARCHAR(max),
     @pageNumber INT = 1,
     @pageSize INT = 10,
     @sortColumn NVARCHAR(20),
     @sortDirection INT)
AS
BEGIN
    DECLARE @TableNameLiteral nvarchar(13) = 'TableName'
    DECLARE @ColumnNameLiteral nvarchar(10) = 'ColumnName'

    IF (ISNULL(@sortColumn, '') = '')
    BEGIN
        SET @sortColumn = @TableNameLiteral
    END

    ;WITH KeyTableId AS 
    (
        SELECT 
            T.TableId
        FROM 
            [dbo].[Tables] T 
        INNER JOIN 
            [dbo].[Columns] C ON T.TableId = C.[TableId]
        WHERE
            C.[Key] = 1
    ), ColumnId AS
    (
        SELECT
            [KeyValue] = C.[ColumnName]
            ,[ColumnId] = V.[ColumnId]
            ,V.[Value]
            ,T.[TableName]
            ,C.[ColumnName]
            ,C.[Order]
            ,C.[Key]
        FROM 
            [dbo].[Tables] T 
        INNER JOIN 
            [dbo].[Columns] C ON T.TableId = C.[TableId]
        LEFT JOIN 
            [dbo].[RowValues] V ON V.[ColumnId] = C.[ColumnId]
        RIGHT JOIN 
            KeyTableId KT ON T.TableId = KT.TableId
    )
    SELECT * 
    FROM ColumnId
    WHERE ISNULL(@SearchTerm,'') = ''
       OR [Value] LIKE @SearchTerm + '%'
    ORDER BY 
        CASE WHEN @sortDirection = 2 THEN
            CASE 
                WHEN @sortColumn = @TableNameLiteral THEN [TableName]
                WHEN @sortColumn = @ColumnNameLiteral THEN [ColumnName]
            END
        END DESC,
        CASE WHEN @sortDirection = 1 THEN
            CASE 
                WHEN @sortColumn = @TableNameLiteral THEN [TableName]
                WHEN @sortColumn = @ColumnNameLiteral THEN [ColumnName]
            END
        END ASC
    OFFSET ((@pageNumber - 1) * @pageSize) ROWS
    FETCH NEXT @PageSize ROWS ONLY; 


    SELECT 
        ColumnId = C.ColumnId,
        C.ColumnName,
        C.[Order],
        C.[Key]
    FROM [dbo].[Tables] T 
        LEFT JOIN [dbo].[Columns] C ON T.TableId = C.[TableId]


END

根据架构，您可能会理解我正在尝试构建的内容。我的问题是如何根据动态名称对结果进行排序

示例（按“学生姓名”DESC 排序）

预期产出

KeyValue        ColumnId    Value               TableName       ColumnName      Order   Key
'StudentName'       1       'Student Cathy'     'Student'       'StudentName'    1      1
'StudentName'       1       'Student Ben'       'Student'       'StudentName'    2      1
'StudentName'       1       'Student Alfonso'   'Student'       'StudentName'    3      1
'StudentName'       1       'Student Alex'      'Student'       'StudentName'    4      1
'StudentName'       1       'Student Alan'      'Student'       'StudentName'    5      1
'Grade'             2       '99'                'Student'       'Grade'          5      0   (Alan's Grade)
'Grade'             2       '98'                'Student'       'Grade'          4      0
... Year
... Section
...

如果键 = 学生姓名，我如何按 StudentName 或 Grade 或 Section 或 Year 排序

或者

如果 key = Teachername 由 @sortColumn NVARCHAR(20) 指示，则按 TeacherName 或部门排序，

例子

declare @SearchTerm NVARCHAR(max)=''
declare @pageNumber INT = 1
declare @pageSize INT = 10
declare @sortColumn NVARCHAR(20) ='StudentName'
declare @sortDirection int= 1 -- 0 asc 1 desc

exec [usp_DynamicSearch_Paged] @SearchTerm, @pageNumber, @pageSize, @sortColumn, @sortDirection

标签： sqlsql-servertsql