SQL Server动态数据透视表列名称 [英] SQL Server Dynamic Pivot Column Names

问题描述

出于应用程序的必要性，我必须将查询的列名作为第一行返回. 现在，我必须将该结果PIVOT以便与我的结果集实现UNION，但是困难的部分是:它必须是动态的，因此，如果我向该表中添加新列，则SELECT将使所有名称都经过透视处理.

For the necessity of my application, I must return the column names of a query as the very first row. Now I must PIVOT this result in order to UNION it with my result set, but the difficult part is: it must be dynamic, so if I ever add new columns to this table, the SELECT will bring all the names pivoted.

以下SELECT带给我列名:

The following SELECT brings me the Column names:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Codes'
ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION 

我的结果集是:

COLUMN_NAME
Id
CodeName
Country
StartDate
EndDate 

我期望的是:

Id    CodeName    Country    StartDate    EndDate (... whatever other columns I might have)

有没有一种简便的方法可以不对列名进行硬编码?

Is there any easy way to do that without hardcoding the column names?

提前谢谢！

推荐答案

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + COLUMN_NAME + ']', 
               '[' + COLUMN_NAME + ']')
               FROM    (SELECT DISTINCT COLUMN_NAME,INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION O 
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = 'CODES') PV  
               ORDER BY O


DECLARE @query NVARCHAR(MAX)
SET @query = '           
              SELECT TOP 0 * FROM 
             (
                 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_NAME = ''CODES''
             ) x
             PIVOT 
             (
                 MIN(COLUMN_NAME)
                 FOR [COLUMN_NAME] IN (' + @cols + ')
            ) p    

            '     
EXEC SP_EXECUTESQL @query

这篇关于SQL Server动态数据透视表列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！