如何在SQL Server 2008函数中动态生成表列定义

Question

在SQL Server 2008中，我有一种情况，我需要返回动态生成的表，并且列也是动态生成的。

其中的所有内容都是由查询生成的，因为我从一个 id 开始，然后获得列名称和类型，这就是强制转换的原因。

以下是最终查询，在一种情况下，它将返回我想要的表，但这个类似的查询可用于返回多个表。

目前这是在 C# 中实现的，但我希望它应该可以在存储过程或函数中实现，但是，我不确定如何进行多个查询来构建此查询，然后返回表。

谢谢。

SELECT ResultID, ResultName, ResultDescription, 
CAST([233] AS Real) as [ResultColA], 
CAST([234] AS Int) as [ResultColB], 
CAST([236] AS NVarChar) as [ResultColC], 
CAST([237] AS Int) as [ResultColD]
FROM (
    SELECT st.*, avt.ResultID as avtID, avt.SomeAttrID, avt.Value 
    FROM Result_AV avt 
    JOIN Result st ON avt.ResultID = st.ResultID) as p 
    PIVOT ( 
        MAX(Value) FOR AttributeID IN ([233], [234], [236], [237])) as pvt

更新：如果我有一个包含车辆制造商的表，并且我想要通用汽车制造的汽车的所有属性。 我会查找汽车的信息，用它来获取所有汽车制造商，即通用汽车，然后我想获取通用汽车制造的所有汽车的信息，但是，由于信息是动态生成的列是不同的。 这就是我所在的地方，需要动态生成该表。 我很好奇是否可以调用 Web 服务，获取动态生成的查询，然后让存储过程或函数执行该查询。

更新 2：获得此表后的下一步是，我需要将汽车上的所有选项相加（在本例中），以确定汽车的实际价格。 这就是为什么它需要采用表格格式。 此时，我正在查看 GM 制造的汽车，但我可以对 SeaRay 制造的船只进行另一个查询，即使船只上的表列与汽车上的表列不同，查询也应该工作相同。

Answer 1

您可以使用动态 SQL 来完成此操作。 您需要使用查询和/或元数据适当地构建两个列表，然后将它们插入到 SQL 中：

DECLARE @template AS varchar(MAX)

SET @template = '
SELECT ResultID, ResultName, ResultDescription
{@SELECT_COLUMN_LIST}
FROM (
    SELECT st.*, avt.ResultID as avtID, avt.SomeAttrID, avt.Value 
    FROM Result_AV avt 
    JOIN Result st ON avt.ResultID = st.ResultID) as p 
    PIVOT ( 
        MAX(Value) FOR AttributeID IN ({@PIVOT_COLUMN_LIST})) as pvt'

DECLARE @sql AS varchar(MAX)

SET @sql = REPLACE(REPLACE(@template, '{@SELECT_COLUMN_LIST}', @SELECT_COLUMN_LIST), '{@PIVOT_COLUMN_LIST}', @PIVOT_COLUMN_LIST)

EXEC (@sql)

要填充 @SELECT_COLUMN_LIST 和 @PIVOT_COLUMN_LIST，您需要有几个选项，但类似这样的东西可以避免对游标或尴尬的字符串操作的任何需要：

SELECT @PIVOT_COLUMN_LIST = COALESCE(@PIVOT_COLUMN_LIST, '') + ',' + QUOTENAME(value)
FROM (SELECT DISTINCT value FROM table)

显然，对于@SELECT_COLUMN_LIST，您需要将此信息与一些元数据结合起来。

为了便于维护，我已经做了类似的事情，因为 PIVOT 和 UNPIVOT 不允许列表是动态 SQL（因为它们会导致输出表上的架构更改）。 对于不解释列的客户端报告样式或列表框用法，这并不重要。