テーブルを動的にピボットし、ゼロパーセンテージの個別のレコードを保持するにはどうすればよいですか?

個別レコードの動的 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 |

解決策:

この問題を解決するには、PIVOT クエリに ROW_NUMBER() 関数を導入します。この関数は、各名前グループ内のレコードに行番号を割り当て、パーセンテージが 0.00 のレコードも確実に保持されます。

;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

説明:

• 最初の共通テーブル式 (cte) はテーブルに ROWNUM 列を追加し、各名前グループ内のレコードをパーセンテージで降順に並べます。
• 2 番目の共通テーブル式 (cte2) は、PIVOT 関数を使用してデータを再構築します。
• 最後に、結果を ID、コード、ROWNUM でグループ化し、ピボット結果を結合します。 0.00 パーセントのレコードを保持します。

重要注:

クエリを動的にするには、ハードコードされた name と name1 の値を、実行時に設定できる動的変数に置き換えます。これにより、クエリでさまざまな数の列を持つテーブルを処理できるようになります。

以上がテーブルを動的にピボットし、ゼロパーセンテージの個別のレコードを保持するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。