Wie erstelle ich eine dynamische PIVOT-Abfrage, die eindeutige Datensätze beibehält?

Dynamische PIVOT-Abfrage zur Beibehaltung eindeutiger Datensätze

Problem:

PIVOT-Abfragen verwenden häufig MAX () Aggregation zur Zusammenfassung von Daten, was zum Verlust unterschiedlicher Werte führen kann. Beispielsweise ignorieren PIVOTs in einer Tabelle mit mehreren Aktivitätsdatensätzen für denselben Namen möglicherweise Datensätze mit niedrigeren MAX()-Werten.

Ziel:

Erstellen Sie einen dynamischen PIVOT Abfrage, die unterschiedliche Datensätze und ihren entsprechenden Prozentsatz behält Werte.

Lösung:

1. ROW_NUMBER()-Partitionierung einführen: ROW_NUMBER() mit Partitionierung nach der Namensspalte hinzufügen, bevor Sie die durchführen PIVOT. Dadurch werden eindeutige Zeilennummern eindeutigen Namenszeilen zugewiesen, während die prozentuale Reihenfolge beibehalten wird.
2. Pivot auf partitionierte Daten: Verwenden Sie ROW_NUMBER() als zusätzliche Spalte in der PIVOT-Abfrage. Die Ausgabe gleicht eindeutige Namenszeilen mit ihrer jeweiligen Aktivität und ihrem jeweiligen Prozentsatz ab.
3. Neugruppierung für dynamische Aggregation: Gruppieren Sie die Daten nach der Pivotierung nach ID, Code und ROW_NUMBER(). Dieser Schritt kombiniert Prozentwerte für jede einzelne Namenszeile.

;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

Dynamische Abfragegenerierung:

Für die dynamische Abfragegenerierung ersetzen Sie die durch Kommas getrennten Listen von Namen (@name_concat, @name1_concat) und SELECT MAX()-Aggregationen (@select_aggs) mit Variablen, die aufgefüllt werden können Laufzeit.

Beispielergebnis:

Die Abfrage gibt die gewünschte Ausgabe zurück, wobei eindeutige Namenszeilen und ihre Prozentsätze erhalten bleiben:

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

Das obige ist der detaillierte Inhalt vonWie erstelle ich eine dynamische PIVOT-Abfrage, die eindeutige Datensätze beibehält?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!