個別のレコードを効果的にピボットする

ピボット クエリは、データを表形式に変換し、簡単なデータ分析を可能にする上で重要な役割を果たします。ただし、個別のレコードを扱う場合、ピボット クエリのデフォルトの動作に問題が生じる可能性があります。

問題: 個別の値の無視

次の表を検討してください:

------------------------------------------------------
| 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 の「欠けている」アクティビティは失われます。

解決策: 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 の「不足」を含むすべてのアクティビティがピボット テーブルに表示されます。この手法により、個別の値が確実に保持され、分析用の正確なデータが提供されます。