Pivotando registros distintos de maneira eficaz

As consultas dinâmicas desempenham um papel crucial na transformação de dados em um formato tabular, permitindo fácil análise de dados. No entanto, ao lidar com registros distintos, o comportamento padrão das consultas dinâmicas pode se tornar problemático.

Problema: ignorando valores distintos

Considere a seguinte tabela:

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

Uma 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

resultaria na seguinte tabela:

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

O problema aqui é que a consulta dinâmica ignora valores distintos para nome1 quando o nome é repetido e a porcentagem é 0. Nesse caso, a atividade "Lacking" para James foi perdida.

Solução: usando ROW_NUMBER() para precisão

Para resolver isso, podemos apresentar ROW_NUMBER():

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

Usando ROW_NUMBER(), particionamos os dados com base no nome e atribuímos a cada linha um número exclusivo dentro dessa partição. Isso nos permite manter a associação entre atividades e percentuais, mesmo quando o nome é repetido.

A tabela 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 as atividades, incluindo "Lacking" para James, agora estão representadas na tabela dinâmica. Essa técnica garante que valores distintos sejam preservados, fornecendo dados precisos para análise.