How to Dynamically PIVOT a Table and Retain Distinct Records with Zero Percentages?

Dynamic PIVOT Query for Distinct Records

Problem:

Consider the following table:

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

Using a standard PIVOT query, it can be challenging to retain distinct records with 0.00 percentages. The MAX function often ignores these rows, resulting in incomplete results.

Expected Result:

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

To resolve this issue, we can introduce a ROW_NUMBER() function into the PIVOT query. This function assigns row numbers to the records within each name group, ensuring that even records with 0.00 percentages are retained.

;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

Explanation:

• The first common table expression (cte) adds a ROWNUM column to the table, ordering the records within each name group by percentage in descending order.
• The second common table expression (cte2) then uses the PIVOT function to restructure the data.
• Finally, we group the results by Id, Code, and ROWNUM to combine the pivot results and retain the 0.00 percentage records.

Important Note:

To make the query dynamic, we can replace the hardcoded name and name1 values with dynamic variables that can be populated at runtime. This allows the query to handle tables with varying numbers of columns.

The above is the detailed content of How to Dynamically PIVOT a Table and Retain Distinct Records with Zero Percentages?. For more information, please follow other related articles on the PHP Chinese website!