Comment faire pivoter dynamiquement une table et conserver des enregistrements distincts avec des pourcentages nuls ?

Requête PIVOT dynamique pour des enregistrements distincts

Problème :

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 |

En utilisant une requête PIVOT standard, cela peut être Il est difficile de conserver des enregistrements distincts avec un pourcentage de 0,00. La fonction MAX ignore souvent ces lignes, ce qui entraîne des résultats incomplets.

Résultat attendu :

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

Solution :

Pour résoudre ce problème, nous pouvons introduire une fonction ROW_NUMBER() dans la requête PIVOT. Cette fonction attribue des numéros de ligne aux enregistrements de chaque groupe de noms, garantissant que même les enregistrements avec un pourcentage de 0,00 sont conservés.

;with cte as (
    select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
    from table_name
),
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

Explication :

• Le la première expression de table commune (cte) ajoute une colonne ROWNUM à la table, classant les enregistrements dans chaque groupe de noms par pourcentage décroissant order.
• La deuxième expression de table commune (cte2) utilise ensuite la fonction PIVOT pour restructurer les données.
• Enfin, nous regroupons les résultats par Id, Code et ROWNUM pour combiner les résultats pivots. et conservez les enregistrements de pourcentage de 0,00.

Important Remarque :

Pour rendre la requête dynamique, nous pouvons remplacer les valeurs name et name1 codées en dur par des variables dynamiques qui peuvent être renseignées au moment de l'exécution. Cela permet à la requête de gérer des tables avec un nombre variable de colonnes.

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!