Pivoter efficacement des enregistrements distincts

Les requêtes pivotantes jouent un rôle crucial dans la transformation des données dans un format tabulaire, permettant une analyse facile des données. Cependant, lorsqu'il s'agit d'enregistrements distincts, le comportement par défaut des requêtes pivot peut devenir problématique.

Problème : ignorer les valeurs distinctes

Considérez le tableau suivant :

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

Une requête pivot traditionnelle, telle que :

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

donnerait le tableau suivant :

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

Le problème ici est que la requête pivot ignore les valeurs distinctes pour nom1 lorsque nom est répété et que le pourcentage est de 0. Dans ce cas, l'activité "Manque" pour James est perdue.

Solution : Utiliser ROW_NUMBER() pour la précision

Pour résoudre ce problème, nous pouvons introduire ROW_NUMBER() :

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

En utilisant ROW_NUMBER(), nous partitionnons les données en fonction du nom et attribuons à chaque ligne un numéro unique au sein de cette partition. Cela nous permet de conserver l'association entre les activités et les pourcentages, même lorsque le nom est répété.

Le tableau résultant sera :

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

Toutes les activités, y compris "Lacking" pour James, sont désormais représentées dans le tableau croisé dynamique. Cette technique garantit que des valeurs distinctes sont préservées, fournissant ainsi des données précises pour l'analyse.