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

I have a table of data, and I allow people to add meta data to that table.

我有一个数据表,我允许人们将元数据添加到该表。

I give them an interface that allows them to treat it as though they're adding extra columns to the table their data is stored in, but I'm actually storing the data in another table.

我给他们一个接口,允许他们对待它,就好像他们正在将数据存储在表中添加额外的列,但我实际上是将数据存储在另一个表中。

Data Table
   DataID
   Data

Meta Table
   DataID
   MetaName
   MetaData

So if they wanted a table that stored the data, the date, and a name, then I'd have the data in the data table, and the word "Date" in metaname, and the date in MetaData, and another row in the meta table with "Name" in metaname and the name in metadata.

因此,如果他们想要一个存储数据,日期和名称的表,那么我将拥有数据表中的数据,以及元数据中的“Date”,以及MetaData中的日期,以及元表中带有“名称”的元表和元数据中的名称。

I now need a query that takes the information from these tables and presents it as though coming from a single table with the two additional columns "Data" and "Name" so to the customer it would look like there's a single table with their custom columns:

我现在需要一个查询来获取这些表中的信息,并将其呈现为来自单个表,其中包含两个附加列“Data”和“Name”,因此对于客户来说,它看起来就像是一个包含自定义列的表:

MyTable
   Data
   Date
   Name

Or, in other words, how do I go from here:

或者,换句话说,我该如何离开这里:

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

To here:

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

Years ago when I did this in MySQL using PHP, I did two queries, the first to get the extra meta data, the second to join them all together. I'm hoping that modern databases have alternate methods of dealing with this.

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

3 个解决方案

#1

You want to pivot each of your name-value pair rows in the MyTable... Try this sql:

您想要在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

#2

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

You'll probably want to add an additional clause (AND Data = 'some value') to return the rows the user is interested in.

您可能希望添加一个附加子句(AND Data ='some value')来返回用户感兴趣的行。

#3

AFAIK, you can do this on the server-side only with a dynamic SQL stored procedure.

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

Effectively the code you want to generate dynamically is:

有效地,您想要动态生成的代码是:

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'

And here's code to do it:

以下是执行此操作的代码:

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)

You could use a similar dynamic technique using the CASE statement example to perform the pivot.

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

#1