有效地透视不同记录

透视查询在将数据转换为表格格式、实现轻松数据分析方面发挥着至关重要的作用。但是，在处理不同记录时，数据透视查询的默认行为可能会出现问题。

问题：忽略不同值

考虑下表：

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

传统的枢轴查询，如：

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

将产生下表：

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

这里的问题是，当 name 重复且百分比为 0 时，数据透视查询会忽略 name1 的不同值。在这种情况下， James 的“Lacking”活动丢失。

解决方案：使用 ROW_NUMBER() 提高准确性

为了解决这个问题，我们可以引入ROW_NUMBER():

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

通过使用 ROW_NUMBER()，我们根据名称对数据进行分区，并为每一行分配该分区内的唯一编号。这使我们能够保留活动和百分比之间的关联，即使名称重复也是如此。

结果表将是：

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

所有活动，包括 James 的“Lacking”，现在都显示在数据透视表中。该技术可确保保留不同的值，为分析提供准确的数据。