SQLShack (日本語)

このシリーズのこれまでのいくつかの記事では、レポートの作成方法の基礎を設定しました。さらに一歩進んで、SQL ServerPIVOTテーブル演算子の使用方法を確認します。単純なクエリから始めて、動的SQLとPIVOTに向かってゆっくりと進んでいきます。始めましょう。

データモデルと一般的な考え方

使用するデータモデルは、このシリーズ全体で使用しているものと同じです。

今日のタスクは、各都市が別々の行にあるレポートを（SQL Server PIVOT演算子を使用して）作成することです。 、および各都市に関連するすべての通話結果の数をカウントします。したがって、すべての結果（辞書のすべての値）はレポートの列になります。

「写真は千の言葉に値する」ので、最終的な目標も写真で説明します。

これを実現するには、call、call_outcome、customer、cityの4つのテーブルのデータを使用する必要があります。以下のモデルを詳しく調べて、これらのテーブルがどのように関連しているかを分析するとよいでしょう。

まず、テーブル内のデータを調べ、クエリごとに、に一歩近づきます。目的の結果（クエリ）。ここで使用するアプローチは、Learn SQL：SQLクエリの記事を使用して手動でレポートを作成する方法と同じです。

レポートのカテゴリとデータ

最初に開始するクエリのセットは、レポートの作成に使用する必要がある4つのテーブルすべてに現在存在するデータを調べるものです。これを実行して、最終的なレポートが正しい値を返したことを確認できるようにします。その後、クエリを作成します。レポートカテゴリとレポートデータ、およびSQL ServerPIVOTテーブルクエリを返します。

これらのクエリは可能な限り単純であるため、構文に関してコメントする特別なことは何もありません。返されるデータに関しては、次の点に注意する必要があります。

• 都市テーブルには6つの都市が含まれているため、最終レポートには6つの行が含まれている必要があります
• call_outcomeテーブルには3つの可能な結果があるため、結果には3つの列が必要です（合計4つの列-> 1つは都市名用です）
• テーブル呼び出しには10行（上の図では8行のみ）なので、最終的な表では、すべての結果の合計は10になります（各呼び出しは正確に1つの結果を持つことができます）

次に行うことレポートカテゴリを準備することです。私たちは、すべての都市とすべての可能な通話結果を組み合わせたいと考えています。これを実現するために、CROSS JOIN（デカルト積）を使用します。

すべてのレポートカテゴリがあることで、次のことが保証されます。そのペアにデータがあるかどうかに関係なく、レポートに行があります。そして、それが私たちが望んでいることです。そのレポートカテゴリに0を表示し、そのカテゴリを完全に見逃さないようにします。

次に行うことは、必要なデータを含む4つのテーブルすべてを結合することです。

クエリ自体は複雑ではありません。その結果、データベースにある呼び出しの数と同じ数の10行があることに気付くはずです。また、DATEDIFF関数を使用して、各呼び出しの期間を秒単位で返しました。ここにcall.idを置くこともできますが、この記事で説明したDATEDIFF関数を思い出したいと思います。各レポートカテゴリ（都市&の呼び出し結果）ごとに費やされる秒数のSUM / AVGが必要な場合は、この期間を使用できます。

SQLServerピボットテーブルを使用しないレポート

これで、カテゴリとデータを結合する準備が整いました。前述の両方のクエリをサブクエリとして使用し、LEFT JOINを使用してそれらを結合します（すべてのカテゴリを最終出力に表示するため）。

18の組み合わせすべて（6つの異なる都市* 3つの異なる呼び出し結果）があり、ここには10の呼び出しすべてが存在します（呼び出し期間が<の行> NULL）。

SQL ServerPIVOTクエリを作成しましょう。

SQL Server PIVOT TABLE（静的）

これまでのところ、必要なデータを取得することができ、それらをリストとして用意しています。データをExcelにエクスポートし、そこで変換を行うことができます。これは、次の記事で実際に行います。それでも、今日は、SQL ServerPIVOT演算子を使用してこれを解決したいと考えています。このセクションでは、「静的」SQL ServerPIVOTについて説明します。最初に使用されるクエリと結果を見てみましょう。

結果がまさに私たちが望んでいたものであることがわかります。各都市が1行にあり、通話結果の3つのカテゴリすべてが別々の列にあります。特定の都市と結果のペアのデータがない場合、そのセル値0を含める必要があります。

SQL Server PIVOTクエリについてコメントしましょう。ここで言及することが重要だと思うことがいくつかあります：

• の最初の部分クエリ– report_data、前のセクションのクエリをコピーして貼り付けました。唯一の変更点は、ここでは適用できないため、クエリのこの部分にORDERBYとGROUPBYがないことです（これらは後に実行する必要があります）。最終的なクエリ結果が生成されます）
• クエリのPIVOT部分は2つの部分で構成されます。最初に、適用する集計関数を定義します。この場合、これは– COUNT（ call_duration）。quのFOR部分でええと、列を定義します。列として必要なすべての値を文学的にリストします。これはハードコーディングです。テーブルに新しい値を追加しても、クエリには影響しません（そしてそうする必要があります）

このクエリはその役割を果たし、変更しない（新しい値を追加）場合は完全に機能します、削除、名前の更新）関連するテーブルの結果を呼び出します。辞書に変更を加える必要がある場合は、これが問題になる可能性があります。期待どおりに機能しないクエリがあるかどうかは考えたくありません。これを解決するには、新しい概念である動的SQLを使用する必要があります。

動的SQLServerピボットテーブル

コードに移る前に、動的SQLが実際に何であるかを簡単に説明しましょう。です（今後の記事で、より詳細なレビューを提供します）。最も簡単な説明は、SQLでは、クエリを文字列として「構築」し、その文字列をSQL Serverストアドプロシージャのパラメータとして渡すことができるということです。このプロシージャは、その文字列に格納されているSQLステートメントを実行します（もちろん、構文に問題がない場合）。

これはあまり一般的に使用されていないように聞こえるかもしれませんが、作業がはるかに楽になる場所がかなりあります。不明なSQL ServerPIVOTテーブルクエリを作成する列の数はまさにそのようなケースの1つです。

このアプローチの考え方は次のとおりです。

• すべての列名（@columns）を格納する変数を宣言します。 ）、および完全なクエリを格納する変数（@query）
• SELECTクエリを使用して、outcome_text列に格納されているすべての値を検索します。次のコード– @columns + = QUOTENAME（TRIM （co.outcome_text））+ 、は、クエリによって返された以前のすべての列名のリストに列名を追加します。その結果、すべての列名がthに格納されます。 e変数@columns
• @query変数には、列が定義されている部分を除いて、前のセクションの完全なクエリを格納します。この部分は変数@columnsから取得します
• 最後に行う必要があるのは、完全なクエリをパラメーターとしてSQLServerシステムプロシージャsp_executesqlに渡すことです

最終結果は前のセクションと同じですが、今回は、に変更を加えてもクエリが機能することを確認します。 result.outcome_text値。また、このクエリを簡単に変更して、他の値を計算することもできます。たとえば、都市と結果のペアごとのSUM / AVG呼び出し時間です。

結論

SQL ServerPIVOT演算子を使用するとデータベースレイヤーで直接達成できることのまったく新しいビュー。動的SQLと組み合わせると、これはさらに進みます。ぜひお試しください。自分で試すよりも良い学習方法はありません。次の記事では、SQLクエリ出力を使用してExcelでテーブルとグラフを作成する方法を示します（これはSQL Serverだけでなく一般的にも機能します）。

目次

SQLの学習：CREATE DATABASE & CREATETABLEの操作

SQLの学習：INSERT INTO TABLE

SQLの学習：プライマリキー

SQLの学習：外部キー

SQLの学習：SELECTステートメント

SQLの学習：内部結合と左結合

SQLの学習：SQLスクリプト

SQLの学習：関係の種類

SQLの学習：参加複数のテーブル

SQLの学習：関数の集約

SQLの学習：複雑なSELECTクエリを作成する方法

SQLの学習：INFORMATION_SCHEMAデータベース

SQLの学習：SQLデータタイプ

SQLの学習：理論の設定

SQLの学習：ユーザー定義関数

SQLの学習：ユーザー定義のストアドプロシージャ

SQLの学習：SQLビュー

SQLの学習：SQLトリガー

SQLの学習：SQLクエリの練習

SQLの学習：SQLクエリの例

SQLの学習：SQLクエリを使用して手動でレポートを作成する

SQLの学習：SQLServerの日付と時刻の関数

SQLの学習：SQLServerレポートの作成日付と時刻関数の使用

SQLの学習：SQLServerピボットテーブル

SQLの学習：SQLServerのExcelへのエクスポート

SQLの学習：SQLServerループの概要

SQLの学習：SQLServerのカーソル

SQLの学習：データの削除と更新に関するSQLのベストプラクティス

SQLの学習：命名規則

SQLの学習：SQL関連のジョブ

SQLの学習：SQLServerでの非等価結合

SQLの学習：SQLインジェクション

• 作成者
• 最近の投稿

Emilはデータベースの専門家ですデータベースに関連するすべての分野で10年以上の経験があります。何年もの間、彼はITおよび金融業界で働き、現在はフリーランサーとして働いています。
彼の過去と現在の取り組みは、データベースの設計とコーディングから、データベースに関する教育、コンサルティング、執筆までさまざまです。また、BI、アルゴリズムの作成、チェス、フィラテリー、犬2匹、猫2匹、妻1匹、赤ちゃん1匹…
LinkedInで彼を見つけることができます
EmilDrkusicによる投稿をすべて表示

Emil Drkusicによる最新の投稿（すべて表示）

• SQLの学習：SQLインジェクション-2020年11月2日
• SQLの学習：SQLServerでの非等価結合-2020年9月29日
• SQLの学習： SQL関連のジョブ-2020年9月1日