script - Qué consulta o vista SQL mostrará "columnas dinámicas"

script data dictionary sql server

Question

Tengo una tabla de datos y les permito a las personas agregar metadatos a esa tabla.

Les doy una interfaz que les permite tratarlo como si estuvieran agregando columnas adicionales a la tabla en la que están almacenados sus datos, pero en realidad estoy almacenando los datos en otra tabla.

Data Table
   DataID
   Data

Meta Table
   DataID
   MetaName
   MetaData

Entonces, si querían una tabla que almacenara los datos, la fecha y un nombre, entonces tendría los datos en la tabla de datos, y la palabra "Fecha" en meta nombre, y la fecha en MetaData, y otra fila en el meta tabla con "Nombre" en meta nombre y el nombre en metadatos.

Ahora necesito una consulta que tome la información de estas tablas y la presente como si procediera de una sola tabla con las dos columnas adicionales "Datos" y "Nombre", de modo que para el cliente parecería que hay una sola tabla con sus columnas personalizadas :

MyTable
   Data
   Date
   Name

O, en otras palabras, ¿cómo voy desde aquí?

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

Hacia aqui:

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

Hace años, cuando hice esto en MySQL usando PHP, hice dos consultas, la primera para obtener metadatos adicionales, la segunda para unirlas todas juntas. Espero que las bases de datos modernas tengan métodos alternativos para tratar con esto.

Relacionado con la opción 3 de esta pregunta .

-Adán

Answer 1

AFAIK, puede hacer esto en el lado del servidor solo con un procedimiento almacenado de SQL dinámico.

Efectivamente, el código que desea generar dinámicamente es:

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''

Y aquí está el código para hacerlo:

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)

Puede usar una técnica dinámica similar utilizando el ejemplo de declaración CASE para realizar el pivote.

Answer 2

Desea pivotar cada una de sus filas de pares de nombre-valor en MyTable ... Pruebe este 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 3

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

Probablemente desee agregar una cláusula adicional (AND Data = ''algún valor'') para devolver las filas que le interesan al usuario.