什么SQL查询或视图将显示“动态列” [英] What SQL query or view will show "dynamic columns"

问题描述

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

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

因此，如果他们想要一个存储数据，日期和名称的表，那么我将数据表中的数据，元日期中的日期，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.

我现在需要一个查询，它从这些表中获取信息，并将其表示为来自具有两个附加列Data和Name的单个表对于客户，它看起来像是一个单独的表及其自定义列：

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:

MyTable
   Data
   Date
   Name

或者换句话说，我如何从这里开始： p>

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

到这里：

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

多年前，当我使用PHP在MySQL中做了这个，我做了两个查询，获取额外的元数据，第二个将它们一起加入。我希望现代数据库有其他处理方法。

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.

与此问题。

- 亚当

推荐答案

您想要在MyTable中旋转每个名称/值对行...试试这个sql：

You want to pivot each of your name-value pair rows in the MyTable... Try this 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

这篇关于什么SQL查询或视图将显示“动态列”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！