آموزش جامع SQL Server – جلسه دهم: عملگرهای CTE، SET و PIVOT
1398/02/31 14:15 , میلاد صاحب نظر

آموزش جامع SQL Server – جلسه دهم: عملگرهای CTE، SET و PIVOT

در این جلسه به چگونگی استفاده از عملگرهای Set برای ترکیب چندین مجموعه نتایج از queryهای ورودی، عبارات رایج جداول (CTE) برای ساده‌تر کردن queryهای پیچیده و دستور PIVOT برای تبدیل ردیف‌ها به ستون‌ها خواهیم پرداخت.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

مطالبی که در این جلسه خواهید آموخت به شرح زیر هستند:

  • UNION – مجموعه نتایج دو یا تعداد بیشتری query را در یک مجموعه نتایج ترکیب می‌کند.

  • INTERSECT – وجه تشابه مجموعه نتایج دو یا تعداد بیشتری query را بازمی‌گرداند.

  • EXCEPT – تفاوت بین دو مجموعه نتایج از دو query ورودی را می‌یابد.

  • CTE، از عبارات رایج جداول برای خواناتر کردن و ساده‌تر کردن queryهای پیچیده استفاده می‌کند.

  • CTE بازگشتی: برای داده‌های سلسله مراتبی یا طبقه‌بندی شده با استفاده از CTE بازگشتی query می‌نویسد.

  • PIVOT: ردیف‌ها را به ستون تبدیل می‌کند.

عملگر UNION در SQL Server

دستور UNION یکی از عملگرهای Set است که به شما اجازه می‌دهد نتایج دو دستور SELECT را درون یک مجموعه نتیجه ترکیب می‌کند و شامل تمام ردیف‌هایی است که متعلق به دستورات SELECT قرار گرفته در عملگر UNION هستند.

کد زیر syntax عملگر UNION در SQL Server را نشان می‌دهد:

query_1
UNION
query_2

دو نکته زیر، مواردی هستند که query باید در syntax بالا از آن‌ها برخوردار باشند:

  • تعداد و ترتیب ستون‌ها باید در هر دو queryها یکسان باشد.
  • نوع‌های داده ستون‌های مشابه باید یکسان یا سازگار باشند.

نمودار دایره‌ای زیر نشان‌ می‌دهد که مجموعه نتیجه جدول T1 چگونه با مجموعه نتیجه جدول T2 ترکیب می‌شود:

نمودار دایره‌ای UNION

UNION در برابر UNION ALL

به طور پیش‌فرض، عملگر UNION تمام ردیف‌های تکراری را از مجموعه نتایج حذف می‌کند. به هر حال، اگر بخواهید ردیف‌های تکراری را نیز بازگردانید، باید همان‌طور که در کد زیر مشاهده می‌کنید، کلیدواژه ALL را نیز به کار ببرید:

query_1
UNION ALL
query_2

به عبارت دیگر، عملگر UNION ردیف‌های تکراری را حذف می‌کند در حالی که عملگر UNION ALL ردیف‌های تکراری را هم در مجموعه نتیجه نهایی درج می‌کند.

UNION در برابر JOIN

دستورات JOIN مانند INNER JOIN یا LEFT JOIN، ستون‌ها را از دو جدول با هم ترکیب می‌کنند، در حالی که عملگر UNION ردیف‌های دو query را با هم ترکیب می‌کند.

به عبارت دیگر، دستور JOIN مجموعه نتایج را به صورت افقی ترکیب می‌کند، در حالی که عملگر UNION مجموعه نتایج را به صورت عمودی ترکیب می‌کند.

تصویر زیر تفاوت اصلی بین UNION و JOIN را نشان می‌دهد:

تفاوت بین UNION و JOIN

مثال‌های عملگر UNION در SQL Server

جداول Staffs و customers از پایگاه‌ داده نمونه Bikestores را در نظر بگیرید:

جداول Staffs و customers

مثال‌های UNION و UNION ALL

مثال زیر اسامی کارکنان و مشتریان را در یک لیست ترکیب می‌کند:

SELECT
    first_name,
    last_name
FROM
    sales.staffs
UNION
SELECT
    first_name,
    last_name
FROM
    sales.customers;
نتایج

مجموعه نتیجه این query شامل ۱۴۵۴ ردیف است.

جدول staffs دارای ۱۰ ردیف است و جدول customers شامل ۱۴۴۵ ردیف که در queryهای زیر نمایش داده شده‌اند:

SELECT
    COUNT (*)
FROM
    sales.staffs;
-- 10       
 
SELECT
    COUNT (*)
FROM
    sales.customers;
-- 1454

چون مجموعه نتیجه UNION فقط ۱۴۵۴ ردیف بازمی‌گرداند، بدان معنا است که ردیف‌های تکراری حذف شدند.

برای درج کردن ردیف‌های تکراری در مجموعه نتیجه می‌توانید همانند کد زیر از دستور UNION ALL استفاده کنید:

SELECT
    first_name,
    last_name
FROM
    sales.staffs
UNION ALL
SELECT
    first_name,
    last_name
FROM
    sales.customers;

این query همان‌طور که انتظار می‌رود، تعداد ۱۴۵۵ ردیف بازمی‌گرداند.

مثال UNION و ORDER BY

برای مرتب‌سازی مجموعه نتیجه بازگشت داده شده توسط عملگر UNION، باید دستور ORDER BY را همانند کد زیر در آخرین query به کار ببرید:

SELECT
    select_list
FROM
    table_1
UNION
SELECT
    select_list
FROM
    table_2
ORDER BY
    order_list;

برای مثال، برای مرتب‌سازی نام‌ها و نام‌های خانوادگی مشتریان و کارکنان، از query زیر استفاده می‌کنیم:

SELECT
    first_name,
    last_name
FROM
    sales.staffs
UNION ALL
SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;

نتایج

در نتیجه، به صورت کامل آموختید که چگونه می‌توان از عملگر UNION در SQL Server برای ترکیب ردیف‌ها از چند query در یک مجموعه نتیجه استفاده کرد.

دستور INTERSECT در SQL Server

دستور INTERSECT مجموعه نتایج دو یا تعداد بیشتری query را با هم ترکیب کرده و ردیف‌های مجزایی که جزو خروجی هر دو query هستند را بازمی‌گرداند.

کد زیر syntax دستور INTERSECT در SQL Server را نشان می‌دهد:

query_1
INTERSECT
query_2

مشابه عملگر UNION، در queryهای syntax بالا هم باید قوانین زیر رعایت شوند:

  • هر دو query باید دارای تعداد و نظم مشابهی در ستون‌ها باشند.
  • نوع داده ستون‌های مشابه باید یکسان یا سازگار باشند.

تصویر زیر عملکرد عملگر INTERSECT را نشان می‌دهد:

عملکرد عملگر INTERSECT

در این نمودار، ما دو مجموعه نتایج T1 و T2 را داریم:

  • مجموعه نتیجه T1 شامل ۱، ۲ و ۳ است.
  • مجموعه نتیجه T2 شامل ۲، ۳ و ۴ است.

وجه تشابه مجموعه نتایج T1 و T2 ردیف‌های مجزا (یعنی ۲ و ۳) را بازمی‌گرداند.

مثال دستور INTERSECT در SQL Server

Query زیر را در نظر بگیرید:

SELECT
    city
FROM
    sales.customers
INTERSECT
SELECT
    city
FROM
    sales.stores
ORDER BY
    city;

نتایج

اولین query تمام شهرهایی که مشتریان در آن‌ها قرار دارند را می‌یابد و دومین query شهرهای فروشگاه‌ها را می‌یابد. کل query، که در آن از دستور INTERSECT استفاده شده است، شهرهای مشترک مشتریان و فروشگاه‌ها را می‌یابد که منظور شهرهای واقع خروجی هر دو queryها است.

دستور EXCEPT در SQL Server

دستور EXCEPT مجموعه نتایج دو query را با هم مقایسه کرده و ردیف‌های مجزا را از query اول (که در خروجی دومین query وجود نداشته باشند) را بازمی‌گرداند. به عبارت دیگر، دستور EXCEPT مجموعه نتیجه یک query را از یک query دیگر کم یا منها می‌کند.

کد زیر suyntax دستور EXCEPT در SQL Server را نشان می‌‌دهد:

query_1
EXCEPT
query_2

در زیر، قوانین ترکیب مجموعه نتایج دو query در syntax بالا را مشاهده می‌کنید:

  • تعداد و ترتیب ستون‌ها باید در هر دو query یکسان باشد.
  • نوع‌های داده ستون‌های مشابه باید یکسان یا سازگار باشند.

تصویر زیر عملکرد دستور EXCEPT را برای دو مجموعه نتایج T1 و T2 را نشان می‌دهد:

عملکرد دستور EXCEPT

در این نمودار:

  • مجموعه نتیجه T1 شامل ۱، ۲ و ۳ است.
  • مجموعه نتیجه T2 شامل ۲، ۳ و ۴ است.

دستور EXCEPT برای مجموعه نتایج T1 و T2 مقدار ۱ را بازمی‌گرداند که ردیف مجزا از مجموعه نتیجه T1 است و در مجموعه نتیجه T2 وجد ندارد.

مثال دستور EXCEPT در SQL Server

جداول products و order items از پایگاه داده نمونه Bikestores را در نظر بگیرید:

جداول products و order items

الف) مثال EXCEPT ساده

مثال زیر از عملگر EXCEPT برای یافتن محصولاتی که هیچ فروشی نداشته‌اند استفاده می‌کند:

SELECT
    product_id
FROM
    production.products
EXCEPT
SELECT
    product_id
FROM
    sales.order_items;

نتایج

در این مثال، اولین query تمام محصولات را بازمی‌گرداند. دومین query محصولاتی را بازمی‌گرداند که فروش داشته‌اند. در نتیجه، مجموعه نتیجه فقط شامل محصولاتی است که هیچ فروشی ندارند.

ب) مثال EXCEPT همراه با ORDER BY

برای مرتب‌سازی مجموعه نتیجه ایجاد شده توسط عملگر EXCEPT، باید دستور ORDER BY را به آخرین query اضافه کنید. برای مثال، مثال زیر محصولاتی را می‌یابد که هیچ فروشی ندارند و محصولات را با توجه به id به صورت صعودی مرتب می‌کند.

نتایج

به این شکل، چگونگی استفاده از دستور EXCEPT در SQL Server برای ترکیب مجموعه نتایج دو query را آموختید.

CTE در SQL Server

CTE مخفف common table expression یا عبارت مرسوم جداول است. CTE به شما اجازه می‌دهد تا یک مجموعه نتیجه موقتی را تعریف کنید که به صورت موقت در دامنه اجرای یک دستور مانند DELETE، UPDATE، INSERT، SELECT یا MERGE در دسترس خواهد بود.

کد زیر syntax معمول یک CTE در SQL Server را نشان می‌دهد:

ITH expression_name[(column_name [,...])]
AS
    (CTE_definition)
SQL_statement;

در این syntax:

  • ابتدا، نام عبارت یا دستور (expression_name) مشخص می‌شود که بعداً می‌توانید در query به آن اشاره کنید.

  • بعد، لیستی از ستون‌های جدا شده با ویرگول پس از expression_name قرار می‌گیرند. تعداد ستون‌ها باید مشابه تعداد ستون‌های تعریف شده در CTE_definition باشند.

  • سپس، اگر لیست ستون مشخص شده باشد، از کلیدواژه AS پس از نام عبارت یا لیست ستون استفاده می‌شود.

  • بعد، یک عبارت SELECT تعریف می‌شود که مجموعه نتیجه آن، عبارت مرسوم جدول را مقداردهی می‌کند.

  • در آخر، در یک query(در دستور SQL_statement) مانند DELETE، UPDATE، INSERT، SELECT یا MERGE،، به عبارت مرسوم جدول اشاره می‌شود.

ما ترجیح می‌دهیم به جای استفاده از subqueryها، از عبارات جدول مرسوم استفاده کنیم، چون عبارات جدولی مرسوم خواناتر هستند. همچنین از CTE در queryهایی که شامل توابع analytic (یا توابع window) هستند نیز استفاده می‌کنیم.

مثال‌های CTE در SQL Server

اجازه دهید چند مثال برای استفاده از عبارات جداول مرسوم ارائه دهیم.

الف) مثال ساده CTE در SQL Server

این query از یک CTE برای بازگرداندن میزان فروش بر اساس کارکنان فروش در سال ۲۰۱۸ استفاده می‌کند:

WITH cte_sales_amounts (staff, sales, year) AS (
    SELECT    
        first_name + ' ' + last_name, 
        SUM(quantity * list_price * (1 - discount)),
        YEAR(order_date)
    FROM    
        sales.orders o
    INNER JOIN sales.order_items i ON i.order_id = o.order_id
    INNER JOIN sales.staffs s ON s.staff_id = o.staff_id
    GROUP BY 
        first_name + ' ' + last_name,
        year(order_date)
)
 
SELECT
    staff, 
    sales
FROM 
    cte_sales_amounts
WHERE
    year = 2018;

تصویر زیر مجموعه نتیجه را نشان می‌دهد:

نتایج

در این مثال:

  • اول، cte_sales_amounts را به عنوان عبارت جدولی رایج تعریف کردیم. CTE نتیجه‌ای را بازگرداند که شامل سه ستون year، staff و Sales است که از query تعریف مشتق شده‌اند.

  • دوم، یک query ایجاد کردیم که با به دست آوردن داده‌ها از جداول order_ites، orders و staffs، میزان کل فروش را بر اساس کارکنان فروش و سال بازمی‌گرداند.

  • سوم، در query بیرونی به CTE اشاره کردیم و فقط ردیف‌هایی که سال آن‌ها ۲۰۱۸ است را انتخاب کردیم.

توجه کنید که این مثال صرفاً برای هدف آموزشی ارائه شده است تا به شما کمک کند تا به خوبی درک کنید که عبارات جدولی مرسوم چگونه عمل می‌کنند. مسلماً بدون استفاده از CTE یک روش خیلی بهینه‌تری برای دستیابی به نتایج وجود دارد.

ب) استفاده از یک عبارت جدولی مرسوم برای گزارش میانگین‌ها بر اساس تعداد یا شمارش

این مثال از CTE برای بازگرداندن تعداد میانگین سفارشات فروش در سال ۲۰۱۸ برای تمام کارمندان فروش استفاده می‌کند:

WITH cte_sales AS (
    SELECT 
        staff_id, 
        COUNT(*) order_count  
    FROM
        sales.orders
    WHERE 
        YEAR(order_date) = 2018
    GROUP BY
        staff_id
 
)
SELECT
    AVG(order_count) average_orders_by_staff
FROM 
    cte_sales;

خروجی به این شکل است:

average_orders_by_staff
-----------------------
48
 
(1 row affected)

در این مثال:

اول، از cte_sales به عنوان نام عبارت جدولی مرسوم استفاده کردیم. از لیست ستون CTE گذشتیم، چون از عبارت تعریف CTE مشتق می‌شود. در این مثال، این لیست شامل ستون‌های staff_id و order_count است.

دوم، از query زیر برای تعریف مجموعه نتیجه‌ای استفاده کردیم که عبارت جدولی مرسوم cte_sales را مقداردهی می‌کند. این Query تعداد سفارش‌ها در سال ۲۰۱۸ را بر اساس کارکنان فروش بازمی‌گرداند.

SELECT    
    staff_id, 
    COUNT(*) order_count
FROM    
    sales.orders
WHERE 
    YEAR(order_date) = 2018
GROUP BY 
    staff_id;

سوم، در دستور بیرونی به cte_sales اشاره کردیم و از تابع ()AVG برای به دست آوردن میانگین فروش‌ها استفاده کردیم که بر اساس تمام کارکنان مرتب شده است.

SELECT
    AVG(order_count) average_orders_by_staff
FROM 
    cte_sales;
پ)مثال استفاده از چندین CTE در یک query

مثال زیر از دو CTE به نام‌های cte_category_counts و cte_category_sales برای بازگرداندن تعداد محصولات و فروش‌ها برای هر دسته از محصولات استفاده می‌کند. Query بیرونی دو CTE را با استفاده از ستون category_id با هم join می‌کند.

WITH cte_category_counts (
    category_id, 
    category_name, 
    product_count
)
AS (
    SELECT 
        c.category_id, 
        c.category_name, 
        COUNT(p.product_id)
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
    GROUP BY 
        c.category_id, 
        c.category_name
),
cte_category_sales(category_id, sales) AS (
    SELECT    
        p.category_id, 
        SUM(i.quantity * i.list_price * (1 - i.discount))
    FROM    
        sales.order_items i
        INNER JOIN production.products p 
            ON p.product_id = i.product_id
        INNER JOIN sales.orders o 
            ON o.order_id = i.order_id
    WHERE order_status = 4 -- completed
    GROUP BY 
        p.category_id
) 
 
SELECT 
    c.category_id, 
    c.category_name, 
    c.product_count, 
    s.sales
FROM
    cte_category_counts c
    INNER JOIN cte_category_sales s 
        ON s.category_id = c.category_id
ORDER BY 
    c.category_name;

خروجی به این شکل است:

نتایج

در نتیجه، به طور کامل و شامل آموختید که چگونه می‌توان از عبارات جدولی مرسوم یا CTE در SQL Server برای ایجاد queryهای پیچیده به شکلی که به آسانی قابل فهم باشد، استفاده کرد.

CTE بازگشتی در SQL Server

CTE بازگشتی به CTE گفته می‌شود که به خودش رفرنس یا اشاره می‌کند. در چنین وضعیتی، CTE پشت سر هم اجرا می‌شود و زیرمجموعه‌های داده بازمی‌گرداند تا اینکه یک مجموعه نتیجه کامل را بازمی‌گرداند.

یک CTE بازگشتی برای query نویسی برای داده‌های سلسله مراتبی (مانند چارت‌های سازمانی که در آن‌ها یک کارمند به یک مدیر گزارش می‌دهد، یا قبض چند سطحی مواد در زمانی که یک محصول شامل چندین قطعه است و هر قطعه هم به خودی خود شامل چندین قطعه دیگر است) مفید است.

کد زیر syntax برای CTE بازگشتی را نشان می‌دهد:

WITH expression_name (column_list)
AS
(
    -- Anchor member
    initial_query  
    UNION ALL
    -- Recursive member that references expression_name.
    recursive_query  
)
-- references expression name
SELECT *
FROM   expression_name

در کل، یک CTE بازگشتی دارای سه بخش است:

  1. یک query آغازین که مجموعه نتیجه پایه و ابتدایی CTE را بازمی‌گرداند. Query آغازین با نام عضو anchor یا تکیه‌گاه نیز شناخته می‌شود.

  2. در نتیجه به یک query بازگشتی که به عبارت جدولی مرسوم رفرنس می‌کند، عضو بازگشتی گفته می‌شود. عضو بازگشتی با استفاده از عملگر UNION ALL به عوض anchor یا تکیه‌گاه union شده است.

  3. یک شرط پایان دادن در عضو بازگشتی مشخص شده است که به اجرای عضو بازگشتی پایان می‌دهد.

ترتیب اجرای یک CTE بازگشتی به شکل زیر است:

  • اول، عضو anchor‌یا تکیه‌گاه اجرا می‌شود تا مجموعه نتیجه پایه و اولیه (R0) ایجاد شود و از این نتیجه برای تکرار بعدی استفاده می‌شود.

  • دوم، عضو بازگشتی با مجموعه نتیجه ورودی از تکرار قبلی (Ri-1) اجرا می‌شود و یک زیرمجموعه از نتایج (Ri) بازمی‌گرداند تا اینکه شرط پایان برقرار شود.

  • سوم، تمام مجموعه نتیجه‌های R0, R1, …Rn با استفاده از عملگر UNION ALL ترکیب می‌شوند تا مجموعه نتیجه نهایی اجرا شود.

فلوچارت زیر نحوه اجرای یک CTE بازگشتی را نشان می‌دهد:

نحوه اجرای یک CTE بازگشتی

مثال‌های CTE بازگشتی در SQL Server

اجازه دهید چند مثال با استفاده از CTEهای بازگشتی حل کنیم.

الف) مثال ساده از CTE بازگشتی

این مثال از یک CTE بازگشتی برای بازگرداندن روز‌های هفته از دوشنبه تا شنبه استفاده می‌کند:

WITH cte_numbers(n, weekday) 
AS (
    SELECT 
        0, 
        DATENAME(DW, 0)
    UNION ALL
    SELECT    
        n + 1, 
        DATENAME(DW, n + 1)
    FROM    
        cte_numbers
    WHERE n < 6
)
SELECT 
    weekday
FROM 
    cte_numbers;

مجموعه نتیجه به شکل زیر است:

نتایج

در این مثال:

تابع ()DATENAME نام روز هفته را بر اساس عدد روز هفته بازمی‌گرداند. عضو anchor یا تکیه‌گاه  دوشنبه را بازمی‌گرداند:

SELECT 
    0, 
    DATENAME(DW, 0)

عوض بازگشتی روز بعدی را با شروع از سه شنبه الی یک‌شنبه بازمی‌گرداند.

SELECT    
        n + 1, 
        DATENAME(DW, n + 1)
    FROM    
        cte_numbers
    WHERE n < 6

شرط موجود در شرط WHERE همان شرط پایانی است که وقتی n برابر با ۶ شد، اجرای عضو بازگشتی را متوقف می‌کند.

n < 6
ب) استفاده از یک CTE بازگشتی برای query داده‌های سلسله مراتبی

جدول sales.staffs از پایگاه داده نمونه Bikestores را مشاهده کنید:

جدول sales.staffs

در این جدول، یک کارمند به صفر یا یک مدیر گزارش می‌دهد. یک مدیر ممکن است صفر یا چندین کارمند داشته باشد. مدیر ارشد هیچ مدیری ندارد. ارتباط در مقادیر ستون manager_id مشخص شده است.

اگر یک کارمند به هیچ کارمند دیگری گزارش ندهد (در این مورد یعنی مدیر ارشد باشد)، آنگاه مقدار آن در ستون manager_id برابر با NULL می‌شود.

این مثال از یک CTE بازگشتی برای به دست آوردن تمام زیر دستان مدیر ارشد که مدیر ندارند (یا مقدار ستون manager_id آن‌ها برابر با NULL است) استفاده می‌کند:

WITH cte_org AS (
    SELECT       
        staff_id, 
        first_name,
        manager_id
        
    FROM       
        sales.staffs
    WHERE manager_id IS NULL
    UNION ALL
    SELECT 
        e.staff_id, 
        e.first_name,
        e.manager_id
    FROM 
        sales.staffs e
        INNER JOIN cte_org o 
            ON o.staff_id = e.manager_id
)
SELECT 
*
FROM 
    cte_org;

خروجی به این شکل است:

نتایج

در این مثال، عضو تکیه‌گاه مدیر ارشد را می‌یابد و query بازگشتی زیردستان مدیر ارشد را بازمی‌گرداند و الی آخر.

به این صورت، چگونگی استفاده از CTE بازگشتی در SQL Server را برای به دست آوردن داده‌های سلسله مراتبی آموختید.

عملگر PIVOT در SQL Server

عملگر PIVOT ردیف‌ها را به ستون تبدیل می‌کند.

بیان اهداف این عملگر

برای نمایش اهداف این عملگر، از جداول production.products و production.categories از پایگاه داده نمونه استفاده می‌شود:

جداول production.products و production.categories

Query زیر تعداد محصولات برای هر دسته از محصولات را می‌یابد:

SELECT 
    category_name, 
    COUNT(product_id) product_count
FROM 
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id
GROUP BY 
    category_name;

خروجی به این شکل است:

نتایج

هدف ما این است که اسامی دسته‌ها را از اولین ستون خروجی را به چندین ستون تبدیل کنیم و تعداد محصولات برای هر نام دسته را به شکل زیر شمارش کنیم:

نتایج

علاوه بر این، می‌توانیم سال مدل را برای گروه‌بندی دسته بر اساس سال مدل اضافه کنیم که خروجی آن به شکل زیر است:

نتایج

تعریف عملگر PIVOT در SQL Server

عملگر PIVOT یک عبارت مقدار دار در جدول را می‌چرخاند. این عملگر مقادیر منحصر به فرد در یک ستون را به چندین ستون در خروجی تبدیل می‌کند و عمل جمع‌آوری را روی بقیه مقادیر ستون باقی‌مانده اعمال می‌کند.

برای ایجاد یک query برای pivot جدول باید مراحل زیر را طی کنید:

  • اول، یک مجموعه داده پایه برای pivot کردن انتخاب کنید.

  • دوم، با استفاده از یک جدول مشتق شده یا عبارت جدولی مرسوم (CTE) یک نتیجه موقت ایجاد کنید.

  • سوم، عملگر Pivot را اعمال کنید.

اجازه دهید این مراحل را در مثال زیر اعمال کنیم.

ابتدا، نام دسته و id محصول را از جداول production.products و production.categories را به عنوان داده‌های پایه برای pivot کردن انتخاب می‌کنیم:

SELECT 
    category_name, 
    product_id
FROM 
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id

دوم، با استفاده از جدول مشتق شده یک مجموعه نتیجه موقتی ایجاد می‌کنیم:

SELECT * FROM (
    SELECT 
        category_name, 
        product_id
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) 

سوم، عملگر pivot را اعمال می‌کنیم:

SELECT * FROM   
(
    SELECT 
        category_name, 
        product_id
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN (
        [Children Bicycles], 
        [Comfort Bicycles], 
        [Cruisers Bicycles], 
        [Cyclocross Bicycles], 
        [Electric Bikes], 
        [Mountain Bikes], 
        [Road Bikes])
) AS pivot_table;

این query خروجی زیر را تولید می‌کند:

نتایج

حالا هر ستون اضافی که به لیست انتخابی query که داده‌های پایه را بازمی‌گرداند اضافه کنید، این داده‌ها به صورت خودکار از گروه‌های ردیف در جدول pivot خواهند بود. برای مثال، می‌توانید ستون سال مدل را به query بالا اضافه کنید:

SELECT * FROM   
(
    SELECT 
        category_name, 
        product_id,
        model_year
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN (
        [Children Bicycles], 
        [Comfort Bicycles], 
        [Cruisers Bicycles], 
        [Cyclocross Bicycles], 
        [Electric Bikes], 
        [Mountain Bikes], 
        [Road Bikes])
) AS pivot_table;

خروجی به این شکل است:

نتایج

تولید مقادیر ستون

در query بالا، باید هر نام دسته را در پرانتزهای بعد از عملگر IN به صورت دستی تایپ کنید. برای پرهیز از این کار، می‌توانید از تابع ()QUOTENAME برای تولید لیست نام دسته استفاده کنید و آن‌ها را در query کپی کنید.

اول، لیست نام دسته را تولید کنید:

DECLARE 
    @columns NVARCHAR(MAX) = '';
 
SELECT 
    @columns += QUOTENAME(category_name) + ','
FROM 
    production.categories
ORDER BY 
    category_name;
 
SET @columns = LEFT(@columns, LEN(@columns) - 1);
 
PRINT @columns;

خروجی به این شکل است:

[Children Bicycles],[Comfort Bicycles],[Cruisers Bicycles],[Cyclocross Bicycles],[Electric Bikes],[Mountain Bikes],[Road Bikes]

در این قطعه کد:

  • تابع ()QUTENAME نام دسته را با کروشه احاطه می‌کند مثل [children Bicycles]

  • تابع ()LEFT آخرین ویرگول را از رشته coluns@ حذف می‌کند

سپس، لیست نام دسته را از خروجی کپی کنید و در query پیست کنید.

جداول pivot پویا

اگر یک نام دسته جدید به جدول production.products اضافه کنید، مجبورید دوباره query را بازنویسی کنید که خیلی ایده آل نیست. برای پرهیز از این امر، می‌توانید از SQL پویا برای پویا سازی جدول pivot استفاده کنید.

در این query، به جای انتقال یک لیست ثابت از اسامی دسته‌ها به عملگر PIVOT، ما لیست نام دسته را ایجاد کردیم و آن را به یک عبارت SQL ارسال کردیم و بعد این عبارت را به صورت پویا با استفاده از stored procedure به نام sp_executesql اجرا کردیم.

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';
 
-- select the category names
SELECT 
    @columns+=QUOTENAME(category_name) + ','
FROM 
    production.categories
ORDER BY 
    category_name;
 
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
 
-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
        category_name, 
        model_year,
        product_id 
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN ('+ @columns +')
) AS pivot_table;';
 
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

بسیار خب، به این شکل چگونگی استفاده از Pivot جدول برای تبدیل ردیف‌ها به ستون‌ها را آموختید.

خلاصه

در این جلسه به سه مبحث مهم چگونگی استفاده از عملگرهای Set برای ترکیب چندین مجموعه نتایج از queryهای ورودی، عبارات رایج جداول (CTE) برای ساده‌تر کردن queryهای پیچیده و دستور PIVOT برای تبدیل ردیف‌ها به ستون‌ها آشنا شدید  همراه با مثال به طور کامل استفاده از آن‌ها را آموختید.

در جلسه بعد در مورد مبحث اصلاح یا تغییر داده‌ها صحبت خواهیم کرد.

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات