什么 SQL 查询或视图将显示“动态列”？

Question

我有一个数据表，我允许人们向该表添加元数据。

我为他们提供了一个界面，允许他们将其视为向存储数据的表添加额外的列，但实际上我将数据存储在另一个表中。

Data Table
   DataID
   Data

Meta Table
   DataID
   MetaName
   MetaData

因此，如果他们想要一个存储数据、日期和名称的表，那么我将在数据表中包含数据，在元名称中包含“日期”一词，在元数据中包含日期，以及数据表中的另一行元表，元名称中包含“名称”，元数据中包含名称。

我现在需要一个查询，从这些表中获取信息，并将其呈现为来自带有两个附加列“数据”和“名称”的单个表，因此对于客户来说，看起来就像有一个带有自定义列的表：

MyTable
   Data
   Date
   Name

或者，换句话说，我该如何从这里：

Data Table
   DataID        Data
   1             Testing!
   2             Hello, World!

Meta Table
   DataID        MetaName         MetaData
   1             Date             20081020
   1             Name             adavis
   2             Date             20081019
   2             Name             mdavis

到这里：

MyTable
   Data          Date             Name
   Testing!      20081020         adavis
   Hello, World! 20081019         mdavis

几年前，当我使用 PHP 在 MySQL 中执行此操作时，我做了两个查询，第一个查询获取额外的元数据，第二个查询将它们连接在一起。 我希望现代数据库有处理这个问题的替代方法。

与此问题的选项 3 相关。

-亚当

Answer 1

您想要旋转 MyTable 中的每个名称-值对行...尝试以下 sql：

DECLARE @Data   TABLE (
    DataID      INT IDENTITY(1,1)   PRIMARY KEY,
    Data        VARCHAR(MAX)
)

DECLARE @Meta   TABLE (
    DataID      INT ,
    MetaName    VARCHAR(MAX),
    MetaData    VARCHAR(MAX)
)

INSERT INTO @Data
SELECT 'Data'

INSERT INTO @Meta
SELECT 1, 'Date', CAST(GetDate() as VARCHAR(20))
UNION
SELECT 1, 'Name', 'Joe Test'

SELECT * FROM @Data

SELECT * FROM @Meta

SELECT 
    D.DataID,
    D.Data,
    MAX(CASE MetaName WHEN 'Date' THEN MetaData ELSE NULL END) as Date,
    MAX(CASE MetaName WHEN 'Name' THEN MetaData ELSE NULL END) as Name
FROM
    @Meta M
JOIN    @Data D     ON M.DataID = D.DataID  
GROUP BY
    D.DataID,
    D.Data

Answer 2

SELECT DataTable.Data AS Data, MetaTable.MetaData AS Date, MetaTable.MetaName AS Name
FROM DataTable, MetaTable
WHERE DataTable.DataID = MetaTable.DataID

您可能需要添加一个附加子句（AND Data = 'some value'）来返回用户感兴趣的行。

Answer 3

AFAIK，您只能使用动态 SQL 存储过程在服务器端执行此操作。

实际上，您想要动态生成的代码是：

SELECT [Data Table].*
    ,[MyTable Date].MetaData
    ,[MyTable Name].MetaData
FROM [Data Table]
LEFT JOIN [MyTable] AS [MyTable Date]
    ON [MyTable Date].DataID = [Data Table].DataID
    AND [MyTable Date].MetaName = 'Date'
LEFT JOIN [MyTable] AS [MyTable Name]
    ON [MyTable Name].DataID = [Data Table].DataID
    AND [MyTable Name].MetaName = 'Name'

下面是执行此操作的代码：

DECLARE @sql AS varchar(max)
DECLARE @select_list AS varchar(max)
DECLARE @join_list AS varchar(max)
DECLARE @CRLF AS varchar(2)
DECLARE @Tab AS varchar(1)

SET @CRLF = CHAR(13) + CHAR(10)
SET @Tab = CHAR(9)

SELECT @select_list = COALESCE(@select_list, '')
                        + @Tab + ',[MyTable_' + PIVOT_CODE + '].[MetaData]' + @CRLF
        ,@join_list = COALESCE(@join_list, '')
                        + 'LEFT JOIN [MyTable] AS [MyTable_' + PIVOT_CODE + ']' + @CRLF
                            + @Tab + 'ON [MyTable_' + PIVOT_CODE + '].DataID = [DataTable].DataID'  + @CRLF
                            + @Tab + 'AND [MyTable_' + PIVOT_CODE + '].MetaName = ''' + PIVOT_CODE + '''' + @CRLF
FROM (
    SELECT DISTINCT MetaName AS PIVOT_CODE
    FROM [MyTable]
) AS PIVOT_CODES

SET @sql = 'SELECT [DataTable].*' + @CRLF
            + @select_list
            + 'FROM [DataTable]' + @CRLF
            + @join_list
PRINT @sql
--EXEC (@sql)

您可以使用类似的动态技术，使用 CASE 语句示例来执行数据透视。