动态SQL字段别名映射咨询：如何通过映射表实现自定义表头输出？

Got it, let's adjust your dynamic SQL to return the desired column headers (units, volume, value) instead of the raw field names (f001, f002, f003). The core fix is adding column aliases when building your field list variable.

Modified Dynamic SQL Code #

DECLARE @listStr varchar(MAX); 
-- Updated: Append AS [alias] to each field to rename the output headers
SET @liststr = ( 
    SELECT Units + ' AS units, ' + Volume + ' AS volume, ' + Value + ' AS value' 
    FROM test_pivot_table 
    WHERE country = 'UK' 
    FOR XML path('') 
) 

DECLARE @query varchar(MAX); 
SET @query = 'SELECT ' + @listStr + ' FROM [dbo].[RB_THI_UK_4_4_4_Fact_03012019]'
EXECUTE(@query)

What Changed? #

• Previously, your @listStr was just concatenating the raw field names (e.g., f001,f002,f003). Now we're modifying each field entry to include an alias using the AS keyword.
• The generated @query will now look like:

  SELECT f001 AS units, f002 AS volume, f003 AS value FROM [dbo].[RB_THI_UK_4_4_4_Fact_03012019]
  

• When executed, this will return your existing data rows (40 12 60, 30 9 53) with the custom headers you need.

Note for Flexibility #

If your alias names are stored in test_pivot_table (instead of being hardcoded), you could adjust the query to pull those aliases dynamically. For example, if the table has columns FieldName and FieldAlias, you could aggregate them like:

SET @liststr = (
    SELECT STRING_AGG(FieldName + ' AS ' + FieldAlias, ', ')
    FROM test_pivot_table
    WHERE country = 'UK'
)

(This uses STRING_AGG which is available in SQL Server 2017+; for older versions, the XML path method still works with alias values from the table.)

内容的提问来源于stack exchange，提问作者viyo