Pivoting Distinct Records Effectively

Pivot queries play a crucial role in transforming data into a tabular format, enabling easy data analysis. However, when dealing with distinct records, the default behavior of pivot queries may become problematic.

Problem: Ignoring Distinct Values

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

A traditional pivot query, such as:

SELECT Id,Code,
    MAX(CASE WHEN name = 'James' THEN activity END) AS James,
    MAX(CASE WHEN name1 = 'James_' THEN percentage END) AS James_,
    MAX(CASE WHEN name = 'Sam' THEN activity END) AS Sam,
    MAX(CASE WHEN name1 = 'Sam_' THEN percentage END) AS Sam_,
    MAX(CASE WHEN name = 'Lisa' THEN activity END) AS Lisa,
    MAX(CASE WHEN name1 = 'Lisa_' THEN percentage END) AS Lisa_
FROM A
GROUP BY Id, Code

would result in the following table:

-------------------------------------------------------------------
Id  Code        James    James_  Sam        Sam_    Lisa      Lisa_
-------------------------------------------------------------------
1   Prashant    Running  43.43  Cooking     3.43    Walking   90.34
1   Prashant    Stealing 0.0    NULL        NULL    NULL      NULL
-------------------------------------------------------------------

The issue here is that the pivot query ignores distinct values for name1 when name is repeated and the percentage is 0. In this case, the "Lacking" activity for James is lost.

Solution: Using ROW_NUMBER() for Accuracy

To address this, we can introduce ROW_NUMBER():

;with cte as 
(
    select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
    from A
)
...

By using ROW_NUMBER(), we partition the data based on name and assign each row a unique number within that partition. This allows us to retain the association between activities and percentages, even when name is repeated.

The resulting table will be:

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

All of the activities, including "Lacking" for James, are now represented in the pivoted table. This technique ensures that distinct values are preserved, providing accurate data for analysis.