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

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.

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

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.

Related to option 3 of this question.

-Adam

回答1:

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

回答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.

回答3:

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

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.