Cómo convertir filas en columnas dinámicamente en SQL Server.

Answer

1. 

   davy.ai

   July 20, 2023 at 2:18 pm

   Para obtener los datos en el formato requerido, se puede utilizar la siguiente consulta SQL:

   DECLARE @columns AS NVARCHAR(MAX), @sql AS NVARCHAR(MAX), @resultId AS INT;
   
   SET @resultId = 8; -- establecer aquí el ResultId deseado
   
   SELECT @columns = COALESCE(@columns + ', ','') + QUOTENAME(c.CategoryName)
   FROM Category c
   INNER JOIN Result r ON c.Id = r.CategoryId
   WHERE r.ResultId = @resultId;
   
   SET @sql = '
   SELECT 
       r.ResultId AS Id,
       ' + @columns + ',
       r.Total
   FROM 
   (
       SELECT 
           ResultId,
           CategoryId,
           SUM(Score) AS Score,
           SUM(Attempted) AS Attempted,
           SUM(Total) AS Total
       FROM Result
       WHERE ResultId = ' + CAST(@resultId AS NVARCHAR) + '
       GROUP BY ResultId, CategoryId
   ) p
   PIVOT
   (
       MAX(Score)
       FOR CategoryId IN (' + @columns + ')
   ) AS pvt
   INNER JOIN Result r ON r.CategoryId = p.CategoryId AND r.ResultId = p.ResultId
   ';
   
   EXEC(@sql);
   

   Explicación:

   • Primero, declaramos tres variables: @columns para almacenar los nombres de columna para la tabla pivote basada en los nombres de categoría en la tabla Category, @sql para construir la consulta SQL dinámica y @resultId para establecer el ResultId deseado.

   • Luego, utilizamos una consulta para seleccionar los nombres de columna de la tabla Category basados en CategoryId y ResultId en la tabla Result. La función COALESCE se utiliza para concatenar los nombres de columna separados por ,. El resultado se almacena en la variable @columns.

   • A continuación, construimos la consulta SQL dinámica utilizando @columns. La consulta primero utiliza una subconsulta (p) para calcular el Score, Attempted y Total totales para cada combinación de CategoryId y ResultId en la tabla Result. Luego, la subconsulta se pivota utilizando la cláusula PIVOT para transformar las filas en columnas. Finalmente, la tabla pivote se une con la tabla Result para obtener la columna Total y filtrar las filas por el ResultId.

   • Por último, ejecutamos la consulta SQL dinámica utilizando la función EXEC.

   Nota: La consulta está parametrizada, por lo que puedes establecer el ResultId deseado cambiando el valor de @resultId. Además, esta consulta asume que la columna CategoryName es única en la tabla Category. Si eso no es así, es posible que debas modificar la variable @columns en consecuencia.

Comments are closed.