¿Es UNPIVOT la mejor manera de convertir columnas en filas?

Para comenzar, definamos y resaltemos la diferencia entre PIVOT y UNPIVOT en SQL Server.

Los operadores relacionales PIVOT y UNPIVOT se utilizan para cambiar una expresión tabular a otra tabla e incluir la rotación de datos. Comencemos con el primer operador.

Usamos PIVOT cuando es necesario convertir las filas de la tabla en columnas. También nos permite realizar agregaciones, cuando sea necesario, para los valores de columna esperados en la salida final. Tomemos esta tabla como ejemplo:

Si lo pasamos a la primera columna "pantalla", obtenemos el siguiente resultado:

Para lograr este resultado en SQL Server, debe ejecutar el siguiente script:

SELECT [avg_], [11], [12], [13], [14], [15] 
FROM (
  SELECT 'average price' AS 'avg_', screen, price FROM laptops) x
  PIVOT (AVG(price) FOR screen IN([11], [12], [13], [14], [15])
) pvt;

Para deshacer la instrucción PIVOT, es decir, para convertir los datos del nivel de columna nuevamente al nivel de fila y obtener la tabla original, puede usar la instrucción UNPIVOT. Sin embargo, tenga en cuenta que UNPIVOT no es todo lo contrario de la función PIVOT. Esto solo es posible si la tabla dinámica no contiene datos agregados.

PIVOT combina datos y puede combinar un montón de filas en una fila. UNPIVOT no reproduce el resultado inicial de una expresión con un valor tabular, ya que las filas se han fusionado. Además, los valores cero en la entrada UNPIVOT desaparecen en la salida. Si los valores desaparecen, esto indica que antes de la operación PIVOT la entrada podría haber sido los valores cero originales.

Hablando del operador PIVOT, dbForge Studio para SQL Server proporciona una función útil llamada tabla dinámica. Para aclarar, es una herramienta de análisis de datos que convierte grandes cantidades de datos en resúmenes breves e informativos. Esto nos permite reorganizar y colapsar fácilmente los datos para obtener un diseño que sea mejor para comprender las relaciones y dependencias de los datos. La mayor ventaja de esta característica es que simplifica el proceso de agregación y cálculo de información estadística. Además, el informe recibido se puede imprimir, exportar en varios formatos de documentos y enviar por correo electrónico en el formato necesario.

Ahora hablemos con más detalle sobre las diferentes implementaciones de las transformaciones T-SQL UNPIVOT.

Supongamos que tenemos una tabla dinámica que contiene datos sobre los resultados de los juegos jugados por cada jugador. Nos enfrentamos a la tarea de convertir columnas en filas.

IF OBJECT_ID ('dbo.Players') IS NOT NULL
    DROP TABLE dbo.Players;
 
CREATE TABLE dbo.Players
(
      PlayerID INT
    , Win INT
    , Defeat INT
    , StandOff INT
    , CONSTRAINT PK_Players PRIMARY KEY CLUSTERED (PlayerID) ON [PRIMARY]
);
INSERT INTO dbo.Players (PlayerID, Win, Defeat, StandOff)
VALUES
    (1, 7,  6,  9),
    (2, 12, 5,  0),
    (3, 3,  11, 1);

Hay varias formas de realizar esta tarea, así que echemos un vistazo a los planes de ejecución para cada una de las implementaciones sugeridas a continuación. Para esto usaremos SQL Profiler está disponible en dbForge Studio para SQL Server.

Para obtener planes de ejecución automáticamente cada vez que se ejecuta una consulta, debemos cambiar al modo de creación de perfiles:

También puede obtener un plan de consulta sin comenzar a ejecutarlo. Para ello, debe ejecutar el comando Generar plan de ejecución.

¡Empecemos!

Índice

    1. UNIÓN DE TODOS

    Anteriormente, SQL Server no ofrecía una forma eficaz de convertir columnas en filas. Debido a esto, muchos usuarios han optado por múltiples lecturas de una sola tabla con un conjunto diferente de columnas combinadas a través de sentencias UNION ALL:

    SELECT PlayerID, GameCount = Win, GameType="Win"
    FROM dbo.Players
        UNION ALL
    SELECT PlayerID, Defeat, 'Defeat'
    FROM dbo.Players
        UNION ALL
    SELECT PlayerID, StandOff, 'StandOff'
    FROM dbo.Players

    Una debilidad importante de esta práctica es la lectura repetida de datos. Esto se debe a que UNION ALL escaneará las filas una vez por cada subconsulta, lo que reduce en gran medida la eficiencia de la ejecución de consultas.

    Esto es obvio cuando miramos el plan para cumplir con la siguiente consulta:

    2. NEPIVAT

    Una de las formas más rápidas de convertir columnas en filas es definitivamente usar la declaración UNPIVOT, que se introdujo en SQL Server en 2005. Simplifiquemos la consulta anterior con esta sintaxis SQL UNPIVOT:

    SELECT PlayerID, GameCount, GameType
    FROM dbo.Players
    UNPIVOT (
        GameCount FOR GameType IN (
            Win, Defeat, StandOff
        )
    ) unpvt

    Como resultado de la ejecución de la solicitud recibimos el siguiente plan de ejecución:

    3. Valores

    Otro punto a considerar es la capacidad de realizar la tarea de convertir columnas en filas usando el operador VALUES.

    La consulta utilizada por la instrucción VALUES se verá así:

    SELECT t.*
    FROM dbo.Players
    CROSS APPLY (
        VALUES
              (PlayerID, Win,      'Win')
            , (PlayerID, Defeat,   'Defeat')
            , (PlayerID, StandOff, 'StandOff')
    ) t(PlayerID, GameCount, GameType)

    Además, el plan de implementación será más simple en comparación con UNPIVOT:

    4. SQL dinámico

    El uso de SQL dinámico le permite crear una consulta de propósito general para cualquier tabla, siempre que las columnas no incluidas en la clave principal tengan un tipo de datos compatible entre ellas:

    DECLARE @table_name SYSNAME
    SELECT @table_name="dbo.Players"
     
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = '
    SELECT *
    FROM ' + @table_name + '
    UNPIVOT (
        value FOR code IN (
            ' + STUFF((
        SELECT ', [' + c.name + ']'
        FROM sys.columns c WITH(NOLOCK)
        LEFT JOIN (
            SELECT i.[object_id], i.column_id
            FROM sys.index_columns i WITH(NOLOCK)
            WHERE i.index_id = 1
        ) i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id
        WHERE c.[object_id] = OBJECT_ID(@table_name)
            AND i.[object_id] IS NULL
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '
        )
    ) unpiv'
     
    PRINT @SQL
    EXEC sys.sp_executesql @SQL

    Y como resultado resulta la siguiente solicitud:

    SELECT *
    FROM <table_name>
    UNPIVOT (
        value FOR code IN (<unpivot_column>)
    ) unpiv

    Este método no es tan rápido porque la generación automática de una consulta UNPIVOT requiere una lectura adicional de las vistas del sistema y la concatenación de cadenas mediante trucos XML.

    5.XML

    Una forma más inteligente de ejecutar un UNPIVOT dinámico es hacer un pequeño truco con XML:

    SELECT
          p.PlayerID
        , GameCount = t.c.value('.', 'INT')
        , GameType = t.c.value('local-name(.)', 'VARCHAR(10)')
    FROM (
        SELECT
              PlayerID
            , [XML] = (
                    SELECT Win, Defeat, StandOff
                    FOR XML RAW('f'), TYPE
                )
        FROM dbo.Players
    ) p
    CROSS APPLY p.[XML].nodes('f/@*') t(c)

    En la consulta anterior, SQL Server creó un XML específico para cada fila:

    < f Column1="Value1" Column2="Value2" Column3="Value3" ... />

    Luego se analiza el nombre del atributo y su valor. En la mayoría de los casos, el uso de XML da como resultado una ejecución más lenta del plan. En nuestro caso, este es el precio que pagamos para que sea universal.

    Comparemos los resultados ejecutando el comando "Comparar resultados seleccionados":

    Debo decir que no hay una diferencia obvia en la velocidad de ejecución de las solicitudes UNPIVOT y VALUES. Esto es cierto para los casos en los que hay una simple conversión de columnas a filas.

    Consideremos otra tarea en la que necesita encontrar el resultado más frecuente del juego para cada jugador.

    Intentaremos realizar esta tarea con la ayuda del operador UNPIVOT:

    SELECT
          PlayerID
        , GameType = (
            SELECT TOP 1 GameType
            FROM dbo.Players
            UNPIVOT (
                GameCount FOR GameType IN (
                    Win, Defeat, StandOff
                )
            ) unpvt
            WHERE PlayerID = p.PlayerID
            ORDER BY GameCount DESC
        )
    FROM dbo.Players p

    El plan de ejecución muestra que el cuello de botella es la lectura y clasificación repetidas de los datos necesarios para organizar las filas de datos:

    Es fácil deshacerse de la lectura repetida de datos si recuerda que puede usar columnas del bloque de consulta externo:

    SELECT
          p.PlayerID
        , GameType = (
            SELECT TOP 1 GameType
            FROM (SELECT t = 1) t
            UNPIVOT (
                GameCount FOR GameType IN (
                    Win, Defeat, StandOff
                )
            ) unpvt
            ORDER BY GameCount DESC
        )
    FROM dbo.Players p

    Se ha eliminado la lectura repetida de datos, pero la operación que consume más recursos, la clasificación, permanece:

    Y así es como se comporta la instrucción VALUES durante esta tarea:

    SELECT
          t.PlayerID
        , GameType = (
                SELECT TOP 1 GameType
                FROM (
                    VALUES
                          (Win,  'Win')
                        , (Defeat,   'Defeat')
                        , (StandOff, 'StandOff')
                ) t (GameCount, GameType)
                ORDER BY GameCount DESC
            )
    FROM dbo.Players t

    Como esperábamos, el plan se ha simplificado, pero la clasificación sigue presente:

    Tratemos de evitar ordenar con la función de agregación:

    SELECT
          t.PlayerID
        , GameType = (
                SELECT TOP 1 GameType
                FROM (
                    VALUES
                          (Win,  'Win')
                        , (Defeat,   'Defeat')
                        , (StandOff, 'StandOff')
                ) t (GameCount, GameType)
                WHERE GameCount = (
                    SELECT MAX(Value)
                    FROM (
                        VALUES (Win), (Defeat), (StandOff)
                    ) t(Value)
                )
            )
    FROM dbo.Players t

    Ahora el plan de ejecución se ve así:

    ¡Hurra! Logramos deshacernos de la clasificación.

    Conclusión

    Si necesitamos convertir fácilmente columnas a filas en SQL Server, es mejor usar estructuras UNPIVOT o VALUES.

    Si, después de la conversión, las filas de datos resultantes se van a usar para agregar o clasificar, es mejor usar la estructura VALUES, que en la mayoría de los casos conduce a planes de ejecución más eficientes.

    Para las tablas en las que pueden presentarse diferentes tipos de estructura y el número de columnas no está limitado, se recomienda utilizar XML que, a diferencia del SQL dinámico, se puede utilizar dentro de las funciones de la tabla.

    El análisis paso a paso del plan de ejecución utilizando SQL Profiler en dbForge Studio para SQL Server le permite identificar cuellos de botella en el rendimiento de las consultas.

    Artículos de interés

    Subir