Comment créer une requête PIVOT dynamique qui préserve les enregistrements distincts ?

Requête PIVOT dynamique pour conserver des enregistrements distincts

Problème :

Les requêtes PIVOT utilisent souvent MAX () agrégation pour résumer les données, ce qui peut entraîner la perte de valeurs distinctes. Par exemple, dans une table comportant plusieurs enregistrements d'activité portant le même nom, les PIVOT peuvent ignorer les enregistrements avec des valeurs MAX() inférieures.

Objectif :

Créer un PIVOT dynamique requête qui conserve des enregistrements distincts et leur pourcentage correspondant valeurs.

Solution :

1. Introduire le partitionnement ROW_NUMBER() : Ajoutez ROW_NUMBER() avec le partitionnement par la colonne de nom avant d'effectuer la PIVOTER. Cela attribue des numéros de ligne uniques à des lignes de noms distincts tout en préservant l'ordre en pourcentage.
2. Pivot sur les données partitionnées : Utilisez ROW_NUMBER() comme colonne supplémentaire dans la requête PIVOT. La sortie fera correspondre des lignes de noms distinctes avec leur activité et leur pourcentage respectifs.
3. Regrouper pour l'agrégation dynamique : Après le pivotement, regroupez les données par identifiant, code et ROW_NUMBER(). Cette étape combine les valeurs en pourcentage pour chaque ligne de nom distincte.

;with cte as
(
    select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
    from A
),
cte2 as
(
    SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_
    FROM cte
    PIVOT(MAX(activity)
          FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
          (
          MAX(percentage)
          FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1
)
select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_
from cte2
group by Id, Code, ROWNUM

Génération de requêtes dynamiques :

Pour la génération de requêtes dynamiques, remplacez les listes séparées par des virgules. de noms (@name_concat, @name1_concat) et d'agrégations SELECT MAX() (@select_aggs) avec des variables qui peuvent être renseignées à runtime.

Exemple de résultat :

La requête renvoie le résultat souhaité, en préservant les lignes de noms distinctes et leurs pourcentages :

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!