问如何从SQL查询中以行形式获取列名和值？

EN

-- set up a temporary table for test purpose
CREATE TABLE #table(ID INT, Field1 NVARCHAR(255), Field2 NVARCHAR(255), Field3 NVARCHAR(255), Field4 NVARCHAR(255))
-- set up some test data
INSERT #table
        ( ID, Field1, Field2, Field3, Field4 )
VALUES  ( 1, 'result 1.1',  'result 1.2',  'result 1.3',  'result 1.4')

DECLARE @sqlQueries TABLE (ID INT, SqlText NVARCHAR(255))
INSERT @sqlQueries
        ( ID, SqlText )
VALUES  ( 1, 'SELECT Field1 as FD1, Field2 as FD2 FROM #table' )
         ,( 2, 'SELECT Field3 as FD3, Field4 as FD4 FROM #table' )

DECLARE @sqlText NVARCHAR(MAX)

DECLARE @values TABLE(v1 NVARCHAR(255), v2 NVARCHAR(255))
DECLARE @results TABLE(field NVARCHAR(255), value NVARCHAR(255))

DECLARE @i INT = 1
DECLARE @field1 NVARCHAR(255), @field2 NVARCHAR(255), @startIndex INT, @charsToTake INT
-- begin loop
WHILE @i <= (SELECT MAX(ID) FROM @sqlQueries)
BEGIN
    SELECT @sqlText = SqlText FROM @sqlQueries AS sq WHERE ID = @i
    INSERT @values
    EXEC( @sqlText)

    -- work out the field aliases used in the query. Assume exactly 2 field names, 
    -- and that the queries all follow the exact pattern as in the examples 
    SELECT @startIndex = CHARINDEX('as ', @sqlText) + 3
    SELECT @charsToTake = CHARINDEX(',', @sqlText) - @startIndex
    SELECT @field1 = SUBSTRING(@sqlText, @startIndex, @charsToTake)
    SELECT @startIndex = CHARINDEX('as ', @sqlText, @startIndex) + 3
    SELECT @charsToTake = CHARINDEX(' FROM', @sqlText) - @startIndex
    SELECT @field2 = SUBSTRING(@sqlText, @startIndex, @charsToTake)

    -- get the results of the query, and then for each of the 2 values insert into a result field
    INSERT @results
            ( field, value )
    SELECT @field1, v.v1
    FROM @values AS v
    UNION SELECT @field2, v.v2
    FROM @values AS v

    DELETE @values
    SET @i = @i + 1
END
-- end loop

-- finally select from the results
SELECT * FROM @results AS r

DROP TABLE #table

field   value
FD1     result 1.1
FD2     result 1.2
FD3     result 1.3
FD4     result 1.4

CREATE PROCEDURE YourProcedureName
(
    @ID int
)
AS
BEGIN
    DECLARE @SQLText NVARCHAR(MAX)
    SELECT @SQLText = SQLText FROM SqlQueries WHERE ID = @ID
    IF @SQLText IS NOT NULL
        EXEC sp_executesql @SQLText
END