从查询中将 SQL 结果从"N rows of 1 column"转换为"1 row of N columns"

Question

使用 PIVOT 完成这个看似微不足道的任务应该简单明了 - 但事实并非如此。

当仅限于仅使用"纯"SQL(请参阅下面的其他因素)时，进行转换的最干净方法是什么，不一定使用透视(请参阅下面的其他因素)？

它应该不会影响答案，但请注意，Python 3.X前端用于在MS SQL Server 2012后端上运行SQL查询。

背景：

我需要通过调用 Python 3.x 中的 SQL 代码来创建 CSV 文件。CSV 标题行是根据保存查询结果的 SQL 表的字段(列)名称创建的。
以下 SQL 代码提取字段名称并将它们作为 N 行 1 列返回 - 但我需要它们作为 1 行 N 列。(在下面的示例中，最终结果必须是"A"、"B"、"C"。

CREATE TABLE #MyTable   -- ideally the real code uses "DECLARE @MyTable TABLE"
(           
A  varchar( 32 ),   
B  varchar( 32 ),   
C  varchar( 32 )    
) ;
CREATE TABLE #MetaData  -- ideally the real code uses "DECLARE @MetaData TABLE"
(
NameOfField varchar( 32 ) not NULL
) ;
INSERT INTO #MetaData 
SELECT   name 
FROM     tempdb.sys.columns as X
WHERE   ( object_id = Object_id( 'tempdb..#MyTable' ) )     
ORDER BY column_id ;    -- generally redundant, ensures correct order if results returned in random order
/*
OK so far, the field names are returned as 3 rows of 1 column (entitled "NameOfField"). 
Pivoting them into 1 row of 3 columns should be something simple like:
*/
SELECT NameOfField
FROM #MetaData AS Source
PIVOT
(
COUNT( [ NameOfField ] )  FOR [ NameOfField ] 
IN ( #MetaData )   -- I've tried "IN (SELECT NameOfField FROM #Metadata)"
) AS Destination ;

此错误被引发两次，一次针对 COUNT，一次针对 PIVOT 语句的"FOR"子句：

Msg 207, Level 16, State 1, Line 32  
Invalid column name ' NameOfField'.

如何使用 #Metadata 的内容来使 PIVOT 正常工作？ 还是有另一种简单的方法？

需要注意的其他背景因素：

• OBDC(Python的pyodbc包)用于从Python 3.x前端传递SQL查询，并将结果(游标)返回给Python 3.x前端。 因此，在将结果集返回到 Python 之前，没有机会使用任何类型的手动干预。

• 上述 SQL 代码旨在成为传递给 SQL 的每个查询的标准样板。 代码必须动态地"适应"#MyTable 的结构(例如，如果删除字段B，而在C之后添加D和E，则最终结果必须是"A"，"C"，"D"，"E")。 这意味着表的字段名称绝不能出现在 PIVOT 的 IN 子句中(#MetaData 表旨在提供这些值)。

• 必须使用"标准"SQL。必须避免所有特定于供应商(例如Microsoft)的扩展/实用程序(例如"bcp"，sqlcmd)，除非有非常令人信服的理由使用它们(因为"它在那里"不算在内)。

• 由于已知原因，select 子句(进入 #Metadata)不适用于临时变量 (@MyTable)。是否有适用于临时变量(即@MetaData)的等效选择？

更新：此问题与SQL Server动态透视查询中的问题略有不同。就我而言，我必须保留字段的顺序，这是该问题不需要的。

为什么我需要这样做：

• python代码是面向非技术人员的GUI。他们使用GUI从大量报告中挑选和选择要运行的(甚至所有)SQL报告。
• 像Excel这样的应用程序被用来查看这些文件：为了让我们的用户满意，每个CSV文件都必须有一个标题行。 标题行将由保存查询结果的 SQL 表中的字段名称组成。
• 这些脚本可以随时更改(例如添加/删除列)，恕不另行通知。为了满足我们的用户需求，标题行必须自动"自行调整"以进行相应的更改。下面的 SQL 代码实现了此目的。
• 标题行与查询结果合并(使用 UNION)，形成传递回 Python 的结果集(游标)。然后，Python 处理返回的数据并创建供客户使用的 CSV 文件(包括标题行)。

简而言之：我们有很多网站，许多用户，许多查询。通过让 SQL"动态创建"标题行，我们消除了必须手动管理/协调/向所有受影响方推出 SQL 更改的麻烦。

Answer 1

我不确定什么是"纯"sql。 您指的是 ANSI-92 SQL 吗？

无论如何，如果你可以使用SQL变量，试试这个：

声明@STRING VARCHAR(MAX)

SELECT  @STRING = COALESCE(@STRING + ', ' + '"' + NameOfField + '"', '"' + NameOfField + '"')
FROM    #MetaData  
SELECT   @STRING 
/*
Results:
"A", "B", "C"
*/

Answer 2

致@Tab Alleman，谢谢。 我能够修改 SQL Server 动态 PIVOT 查询的答案？ 以满足我所有需求的方式进行交换(见下文)。

注意：出于某种原因，"DISTINCT"关键字按字母顺序放置字段 - 这是我不想要的。
注释该单词(如下所述)可保留字段的顺序。 我对这样做有点不安，但在这种情况下，它应该是安全的，因为选择 #MetaData 的值保证是唯一的。
通过交换 #MyTable 中的字段A和B并取消注释"DISTINCT"关键字，可以很容易地看到差异。

--drop table #MyTable 
--drop table #MetaData 
Create TABLE #MyTable 
(           
A  varchar( 10 ),
B  varchar( 10 ),
C  varchar( 10 )
)
;
CREATE TABLE #MetaData 
(
NameOfField  varchar( 100 ) not NULL,
Position int
)
;
INSERT INTO #MetaData 
SELECT   name, column_id
FROM     tempdb.sys.columns as X
WHERE    ( object_id = Object_id( 'tempdb..#MyTable' ) ) 
--ORDER BY column_id    -- normally redundant, guards against results being returned in random order
;
select * from #MetaData 
DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);
SET @cols = STUFF( (SELECT 
--                  DISTINCT 
',' + QUOTENAME( c.NameOfField ) 
FROM #MetaData AS  c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
--print( @cols )
set @query = 'SELECT ' + @cols + ' from 
(
select NameOfField
from #MetaData
) AS x
pivot 
(
MAX( NameOfField )
for NameOfField in ( '+ @cols + ' )
) AS p 
'
--print( @query )
execute( @query )
drop table #MyTable 
drop table #MetaData