How Can I Perform a PIVOT Query on Distinct Records While Preserving Associations and Handling Zero Values?

PIVOT Query on Distinct Records with Retained Association

Consider the scenario where we have a table with data containing activities and percentages for individuals. A standard PIVOT query using the MAX function may overlook activities with zero percentage values. To address this issue, we introduce a solution that preserves the relationship between activities and percentages.

To achieve this, we incorporate a ROW_NUMBER() function into the common table expression (CTE) to sequentially order the activities within each name group. With this technique, the PIVOT query can retain the association between activities and percentages across multiple rows.

Here's an example of the revised query:

;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

The CTE, cte, adds a ROWNUM column to the initial table, ordering activities for each name in descending order of percentage. This ordering allows the subsequent PIVOT query to group and aggregate the data correctly.

The output of the query will resemble the expected result, where distinct activities are associated with their respective names and 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

Using this technique, we can obtain complete and accurate PIVOT results while retaining the association between activities and percentages, even for records with zero percentage values.

The above is the detailed content of How Can I Perform a PIVOT Query on Distinct Records While Preserving Associations and Handling Zero Values?. For more information, please follow other related articles on the PHP Chinese website!