前提条件:
1つのテーブルの行がPIVOT()関数のように列に変換される、データベースといくつかの関連テーブルを作成する必要があります。次のSQLステートメントを実行して、「」という名前のデータベースを作成します。 unidb 」と名前を付けた3つのテーブルを作成します 学生 ’、‘ コース ' と ' 結果 '。 学生 と 結果 テーブルは1対多の関係で関連付けられ、 コース と 結果 ここでは、テーブルは1対多の関係で関連付けられます。のCREATEステートメント 結果 テーブルには、フィールドの2つの外部キー制約が含まれています。 std_id 、 と course_id 。
CREATE DATABASE unidb;
unidbを使用します。
CREATETABLEの学生((
idINTプライマリキー、
名前varchar((50)。NULLではありません、
部門VARCHAR((15)。NULLではありません)。;
CREATETABLEコース((
course_id VARCHAR((20)。主キー、
名前varchar((50)。NULLではありません、
クレジットSMALLINTNOT NULL)。;
CREATETABLEの結果((
std_id INT NOT NULL、
course_id VARCHAR((20)。NULLではありません、
mark_type VARCHAR((20)。NULLではありません、
SMALLINT NOTNULLをマークします。
外部キー((std_id)。参考文献学生((id)。、
外部キー((course_id)。参考コース((course_id)。、
主キー((std_id、course_id、mark_type)。)。;
にいくつかのレコードを挿入します 学生、コース、結果 テーブル。値は、テーブル作成時に設定された制限に基づいてテーブルに挿入する必要があります。
学生の価値観に挿入
(( '1937463'、「ハーパー・リー」、「CSE」)。、
(( 「1937464」、「ガルシアマルケス」、「CSE」)。、
(( 「1937465」、「フォースター、E.M。」、「CSE」)。、
(( 「1937466」、「ラルフ・エリソン」、「CSE」)。;
コースの値に挿入
(( 「CSE-401」、'オブジェクト指向プログラミング'、3)。、
(( 「CSE-403」、'データ構造'、2)。、
(( 「CSE-407」、「Unixプログラミング」、2)。;
結果値に挿入
(( '1937463'、「CSE-401」、「内部試験」、15)。、
(( '1937463'、「CSE-401」、'中間試験'、20)。、
(( '1937463'、「CSE-401」、'期末試験'、35)。、
(( 「1937464」、「CSE-403」、「内部試験」、17)。、
(( 「1937464」、「CSE-403」、'中間試験'、15)。、
(( 「1937464」、「CSE-403」、'期末試験'、30)。、
(( 「1937465」、「CSE-401」、「内部試験」、18)。、
(( 「1937465」、「CSE-401」、'中間試験'、2. 3)。、
(( 「1937465」、「CSE-401」、'期末試験'、38)。、
(( 「1937466」、「CSE-407」、「内部試験」、20)。、
(( 「1937466」、「CSE-407」、'中間試験'、22)。、
(( 「1937466」、「CSE-407」、'期末試験'、40)。;
ここ、 結果 テーブルには、の同じ値が複数含まれています std_id 、 mark_type と course_id 各行の列。データをより整理された形式で表示するために、これらの行をこのテーブルの列に変換する方法は、このチュートリアルの次の部分に示されています。
CASEステートメントを使用して行を列にローテーションします。
次の単純なSELECTステートメントを実行して、のすべてのレコードを表示します。 結果 テーブル。
選択する*FROM結果;
出力には、3つのコースの3つの試験タイプに対する4人の学生の点数が表示されます。したがって、 std_id 、 course_id と mark_type さまざまな学生、コース、試験の種類に対して複数回繰り返されます。
CASEステートメントを使用してSELECTクエリをより効率的に記述できる場合、出力はより読みやすくなります。次のSELECTとCASEステートメントは、行の繰り返し値を列名に変換し、テーブルの内容をユーザーにとってより理解しやすい形式で表示します。
SELECT result.std_id、result.course_id、MAX((result.mark_type =の場合「内部試験」次にresult.marksEND)。 「内部試験」、
MAX((result.mark_type =の場合'中間試験'次にresult.marksEND)。 '中間試験'、
MAX((result.mark_type =の場合'期末試験'次にresult.marksEND)。 '期末試験'
FROM結果
GROUP BY result.std_id、result.course_id
ORDER BY result.std_id、result.course_id ASC;
上記のステートメントを実行すると、前の出力よりも読みやすい次の出力が表示されます。
CASEとSUM()を使用して行を列にローテーションします。
テーブルからすべての学生の各コースの総数をカウントする場合は、集計関数を使用する必要があります 和() グループ化 std_id と course_id CASEステートメントを使用します。次のクエリは、SUM()関数とGROUPBY句を使用して前のクエリを変更することによって作成されます。
SELECT result.std_id、result.course_id、MAX((result.mark_type =の場合「内部試験」次にresult.marksEND)。 「内部試験」、
MAX((result.mark_type =の場合'中間試験'次にresult.marksEND)。 '中間試験'、
MAX((result.mark_type =の場合'期末試験'次にresult.marksEND)。 '期末試験'、
和((result.marks)。 なので合計
FROM結果
GROUP BY result.std_id、result.course_id
ORDER BY result.std_id、result.course_id ASC;
出力には、という名前の新しい列が表示されます 合計 これは、特定の各学生が取得した各コースのすべての試験タイプの点数の合計を表示しています。
行を複数のテーブルの列にローテーションします。
前の2つのクエリはに適用されます 結果 テーブル。このテーブルは、他の2つのテーブルに関連しています。これらは 学生 と コース 。学生IDの代わりに学生名を表示し、コースIDの代わりにコース名を表示する場合は、3つの関連テーブルを使用してSELECTクエリを作成する必要があります。 学生 、 コース と 結果 。次のSELECTクエリは、FORM句の後に3つのテーブル名を追加し、WHERE句に適切な条件を設定して、3つのテーブルからデータを取得し、以前のSELECTクエリよりも適切な出力を生成することによって作成されます。
学生の名前を選択しますなので 「学生の名前「、courses.nameなので 「コース名「、MAX((result.mark_type =の場合「内部試験」次にresult.marksEND)。 「CT」、
MAX((result.mark_type =の場合'中間試験'次にresult.marksEND)。 「中」、
MAX((result.mark_type =の場合'期末試験'次にresult.marksEND)。 '最後の'、
和((result.marks)。 なので合計
学生、コース、結果から
WHERE result.std_id = students.idおよびresult.course_id = courses.course_id
GROUP BY result.std_id、result.course_id
ORDER BY result.std_id、result.course_id ASC;
上記のクエリを実行すると、次の出力が生成されます。
結論:
この記事では、MySQLでPivot()関数をサポートせずにPivot()関数の機能を実装する方法を、いくつかのダミーデータを使用して示します。この記事を読んだ後、読者がSELECTクエリを使用して、行レベルのデータを列レベルのデータに変換できるようになることを願っています。