How to Create a Dynamic PIVOT Query that Preserves Distinct Records?

Dynamic PIVOT Query to Preserve Distinct Records

Problem:

PIVOT queries often use MAX() aggregation to summarize data, which can result in the loss of distinct values. For instance, in a table with multiple activity records for the same name, PIVOTs might ignore records with lower MAX() values.

Objective:

Create a dynamic PIVOT query that retains distinct records and their corresponding percentage values.

Solution:

1. Introduce ROW_NUMBER() Partitioning: Add ROW_NUMBER() with partitioning by the name column before performing the PIVOT. This assigns unique row numbers to distinct name rows while preserving percentage ordering.
2. Pivot on Partitioned Data: Use the ROW_NUMBER() as an additional column in the PIVOT query. The output will match distinct name rows with their respective activity and percentage.
3. Regroup for Dynamic Aggregation: After pivoting, group the data by Id, Code, and ROW_NUMBER(). This step combines percentage values for each distinct name row.

;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

Dynamic Query Generation:

For dynamic query generation, replace the comma-separated lists of names (@name_concat, @name1_concat) and SELECT MAX() aggregations (@select_aggs) with variables that can be populated at runtime.

Example Result:

The query returns the desired output, preserving distinct name rows and their percentages:

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

The above is the detailed content of How to Create a Dynamic PIVOT Query that Preserves Distinct Records?. For more information, please follow other related articles on the PHP Chinese website!