Pivotar registros distintos de manera efectiva

Las consultas dinámicas desempeñan un papel crucial en la transformación de datos a un formato tabular, lo que permite un análisis de datos sencillo. Sin embargo, cuando se trata de registros distintos, el comportamiento predeterminado de las consultas dinámicas puede resultar problemático.

Problema: ignorar valores distintos

Considere la siguiente tabla:

------------------------------------------------------
| Id    Code  percentage  name  name1   activity     |
-----------------------------------------------------
| 1   Prashant  43.43    James  James_  Running      |
| 1   Prashant  70.43    Sam    Sam_    Cooking      |
| 1   Prashant  90.34    Lisa   Lisa_   Walking      |
| 1   Prashant  0.00     James  James_  Stealing     |
| 1   Prashant  0.00     James  James_  Lacking      |
| 1   Prashant  73       Sam     Sam_   Cooking 1    |
------------------------------------------------------

Una consulta dinámica tradicional, como:

SELECT Id,Code,
    MAX(CASE WHEN name = 'James' THEN activity END) AS James,
    MAX(CASE WHEN name1 = 'James_' THEN percentage END) AS James_,
    MAX(CASE WHEN name = 'Sam' THEN activity END) AS Sam,
    MAX(CASE WHEN name1 = 'Sam_' THEN percentage END) AS Sam_,
    MAX(CASE WHEN name = 'Lisa' THEN activity END) AS Lisa,
    MAX(CASE WHEN name1 = 'Lisa_' THEN percentage END) AS Lisa_
FROM A
GROUP BY Id, Code

daría como resultado la siguiente tabla:

-------------------------------------------------------------------
Id  Code        James    James_  Sam        Sam_    Lisa      Lisa_
-------------------------------------------------------------------
1   Prashant    Running  43.43  Cooking     3.43    Walking   90.34
1   Prashant    Stealing 0.0    NULL        NULL    NULL      NULL
-------------------------------------------------------------------

El problema aquí es que la consulta dinámica ignora valores distintos para nombre1 cuando el nombre se repite y el porcentaje es 0. En este caso, se pierde la actividad "Faltante" de James.

Solución: usar ROW_NUMBER() para obtener precisión

Para solucionar este problema, podemos introducir ROW_NUMBER():

;with cte as 
(
    select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
    from A
)
...

Al usar ROW_NUMBER(), particionamos los datos según el nombre y asignamos a cada fila un número único dentro de esa partición. Esto nos permite conservar la asociación entre actividades y porcentajes, incluso cuando se repite el nombre.

La tabla resultante será:

----------------------------------------------------------
| Id  Code        James       James_  Sam         Sam_    Lisa    Lisa_
----------------------------------------------------------
| 1   Prashant    Running     43.43   Cooking 1   73      Walking 90.34
| 1   Prashant    Stealing    0.00    Cooking     3.43    NULL    NULL
| 1   Prashant    Lacking     0.00    NULL        NULL    NULL    NULL
----------------------------------------------------------

Todas las actividades, incluida "Falta" para James, ahora están representadas en la tabla dinámica. Esta técnica garantiza que se conserven valores distintos, proporcionando datos precisos para el análisis.