如何動態透視表並保留零百分比的不同記錄？

不同記錄的動態PIVOT 查詢

問題：

問題：

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

使用在標準PIVOT在查詢中，保留具有 0.00 百分比的不同記錄可能具有挑戰性。 MAX 函數經常忽略這些行，導致結果不完整。

預期結果：

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

解：

;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

為了解決這個問題，我們可以在PIVOT中查詢引入ROW_NUMBER()函數。此函數為每個名稱組中的記錄指派行號，確保保留百分比為 0.00 的記錄。

說明：

• The第一個公共表表達式(cte) 將ROWNUM 欄位加入表中，以百分比降序對每個名稱組中的記錄進行排序
• 然後第二個公共表表達式(cte2) 使用PIVOT函數重構資料。

最後，我們按 Id、Code 和 ROWNUM 對結果進行分組，以合併透視結果並保留0.00百分比記錄。

重要注意：

為了使查詢動態化，我們可以將硬編碼的name 和name1 值替換為可以在運行時填充的動態變數。這允許查詢處理具有不同列數的表。

以上是如何動態透視表並保留零百分比的不同記錄？的詳細內容。更多資訊請關注PHP中文網其他相關文章！