PIVOT و UNPIVOT در SQL Server – قسمت اول

PIVOT و UNPIVOT در SQL Server – قسمت اول

نوشته شده توسط: مهدی شیشه بری
۲۵ خرداد ۱۳۹۵
زمان مطالعه: 8 دقیقه
۴.۳
(۱۴)

مقدمه PIVOT و UNPIVOT در SQL Server

فرض کنید جدولی با نام sale و مطابق با چنین ساختاری داریم. این جدول شامل اطلاعات تعداد فروش (amount) بر اساس سال (year) و به ازای هر فصل (quarter) است.
PIVOT و UNPIVOT در SQL Serverحال می خواهیم به عنوان مثال، اطلاعات فروش هر سال را به تفکیک هر فصل داشته باشیم.
برای این کار از Aggregation Functionها استفاده کرده و اسکریپت زیر را اجرا می کنیم:

SELECT year,quarter,SUM(amount) AS amountSum
FROM sale
GROUP BY YEAR,quarter
ORDER BY year
GO

PIVOT و UNPIVOT در SQL Serverخروجی کوئری بالا، اطلاعات فروش هر سال را به تفکیک هر فصل و در قالب یک رکورد نمایش می‌دهد.
در ادامه اگر بخواهیم اطلاعات فروش به ازای هر سال و بر اساس تمامی فصل¬ها صرفا در قالب یک رکورد یا یک سطر نمایش داده شود، باید چه کار کنیم؟
با استفاده از Sub Queryها این کار امکان‌پذیر است!

SELECT DISTINCT year
,(SELECT SUM(amount)FROM sale s2 WHERE s2.year=s1.year AND s2.quarter='spring') AS spring
FROM sale s1
GO

PIVOT و UNPIVOT در SQL Server

 به خروجی کوئری اجرا شده توجه کنید! این کوئری، صرفا جهت نمایش اطلاعات فروش فصل بهار است. بنابراین برای نمایش اطلاعات سایر فصل ها، می‌بایست آنها را در کوئری شرکت داد:

SELECT DISTINCT year
,(SELECT SUM(amount)FROM sale s2 WHERE s2.year=s1.year AND s2.quarter='spring') AS spring
,(SELECT SUM(amount)FROM sale s2 WHERE s2.year=s1.year AND s2.quarter='summer') AS summer
,(SELECT SUM(amount)FROM sale s2 WHERE s2.year=s1.year AND s2.quarter='autumn') AS autumn
,(SELECT SUM(amount)FROM sale s2 WHERE s2.year=s1.year AND s2.quarter='winter') AS winter
FROM sale s1
GO

PIVOT و UNPIVOT در SQL Serverهمان طور که می‌بینید، توانستیم اطلاعات فروش در هر سال و به تفکیک هر فصل را در قالب یک رکورد نمایش دهیم اما نکته قابل تامل این است که اگر تنوع بازه زمانی اطلاعات فروش بر اساس ماه های مختلف در نظر گرفته شده بود آن گاه می‌بایست تمامی ماه های سال را در کوئری شرکت می‌دادیم!
این موضوع در خصوص موجودیت هایی متنوع، قطعا چالش برانگیز خواهد بود و روش بهینه ای به حساب نمی‌آید.
اکنون برای رفع این مشکل چه باید کرد؟ پاسخ SQL Server استفاده از PIVOT Tableها است.

دوره کوئری نویسی نیک آموز

PIVOT Table چیست؟

همان طور که در شکل پایین می‌بینید، خواسته ما، چرخش مقادیر داده ها از درون ستون های جدول به سمت Header گزارش است و این قابلیت به کمک PIVOT Tableها در SQL Server تامین می‌شود. به عبارت دیگر زمانی از PIVOT Tableها استفاده می‌کنیم که بخواهیم گزارش هایی از نوع Cross-Tab داشته باشیم.
 PIVOT و UNPIVOT در SQL Serverالگوی استفاده از PIVOT Tableها در SQL به شکل زیر می‌باشد:

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

بنابراین در ابتدای کار می‌بایست تکلیف سه مورد زیر را مشخص کنیم

۱- Aggregate Column: همان فیلدی است که قرار است بر روی آن عملیات Aggregation انجام شود که در مثال فرضی ما، فیلد amount خواهد بود.
۲- PIVOT Column: فیلدی که قرار است از درون رکوردها به سمت Header گزارش چرخش داشته باشد که در مثال فرضی ما، فیلد quarter خواهد بود. این فیلد در جلو عبارت FOR قرار می‌گیرد.
۳- لیستی که قرار است گزارش براساس آن تهیه شود که در مثال فرضی ما، مقادیر فیلد quarter خواهد بود که همان spring,summer,autumn,winter خواهند بود.

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

SELECT * FROM sale
PIVOT
(SUM (amount) FOR quarter
IN ([spring],[summer],[autumn],[winter]))pTable

PIVOT و UNPIVOT در SQL Serverشکل زیر مقایسه میان Plan اجرایی این کوئری (استفاده از PIVOT) و کوئری قبلی (استفاده از Sub Query) را نشان می‌دهد و شما می‌بینید که به لحاظ کارآیی، استفاده از PIVOT Tableها به چه میزان تاثیر گذار خواهند بود. مقایسه دیاگرام Plan اجرایی این دو کوئری هم در نوع خودش جالب توجه است. از طرفی میزان خطوط نوشته شده در هر کوئری هم جای تامل دارد!
PIVOT و UNPIVOT در SQL Serverضمنا باید به این نکته هم توجه داشته باشید که با استفاده از ایندکس گذاری مناسب، قطعا می‌توانیم به کارآیی بیشتر این گونه کوئری ها کمک کنیم.
در ادمه می‌خواهیم تغییراتی بر روی جدول sale اعمال کنیم. این تغییرات شامل افزودن یک فیلد از نوع INT و با خصوصیت IDENTITY است:

ALTER TABLE sale
ADD id INT IDENTITY

مجددا همان کوئری ای را که در آن از PIVOT استفاده شده بود، اجرا می‌کنیم.
خروجی کوئری، مطابق با آنچه که ما انتظارش را داشتیم، نیست!
PIVOT و UNPIVOT در SQL Serverآیا می‌توان چنین استنباط کرد که قابلیت PIVOT صرفا برای جداول سه فیلدی ایجاد شده است؟ پاسخ مثبت و چنین برداشتی، قطعا موجب رنجش خاطر تیم توسعه دهنده Microsoft SQL Server خواهد شد!

اما بیایید با هم بررسی کنیم که چرا چنین اتفاقی افتاده و راه برون رفت از آن چیست؟

دوباره به کوئری زیر توجه کنید. فرض می‌کنیم هنوز به جدول مان فیلد id را اضافه نکرده ایم. کوئری زیر را اجرا می‌کنیم:

SELECT * FROM sale
PIVOT
(SUM (amount) FOR quarter
IN ([spring],[summer],[autumn],[winter]))pTable

PIVOT و UNPIVOT در SQL Serverدر این کوئری، SQL نتایج را بر اساس سال فروش (year) تفکیک کرده است. اما SQL از کجا تشخیص داده است که باید چنین کاری را انجام بدهد؟ پاسخ آن است که در این حالت تمامی فیلد های یک جدول به غیر از Aggregate Column و PIVOT Column، توسط SQL در GROUP BY شرکت داده می‌شوند که در این جا شامل فیلد year می‌شود.
این موضوع در Plan اجرایی کوئری، به وضوح قابل مشاهده است.
 PIVOT و UNPIVOT در SQL Serverالبته این قاعده در برخی از موارد به ضرر ما تمام می‌شود و این همان جایی است که مثلا به جدول sale یک فیلد id اضافه شود. آن گاه علاوه بر فیلد year، فیلد id هم در GROUP BY شرکت داده می‌شود و نتایج مورد انتظارمان حاصل نخواهد شد.
برای رفع چنین مشکلی می‌بایست به جای استفاده از SELECT ای مستقیم از جدول sale، با استفاده از یک Sub Query، فیلدهای موردنظرمان را در SELECT انتخاب کنیم.
اسکریپت زیر، نحوه انجام کار را به شما نشان می‌دهد:

SELECT * FROM
(SELECT year,quarter,amount FROM sale)s
PIVOT
(SUM(s.amount) FOR quarter
IN ([spring],[summer],[autumn],[winter])
)pTable

 

و اما حالا شما به عنوان تمرین، تلاش کنید کوئری ای بنویسید که خروجی زیر را نمایش دهد:
PIVOT و UNPIVOT در SQL Server

چه رتبه ای می‌دهید؟

میانگین ۴.۳ / ۵. از مجموع ۱۴

اولین نفر باش

title sign
معرفی نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • درود و سپاس از مقاله خوبتان
      من سعی کردم و پاسخ تمرین را بصورت زیر نوشتم :
      SELECT * FROM
      (SELECT CAST(YEAR AS CHAR(4)) YEAR,quarter,amount FROM sale)s
      PIVOT
      (SUM(s.amount) FOR quarter
      IN ([spring],[summer],[autumn],[winter])
      )pTable
      UNION
      SELECT ‘Totalsum’,* FROM
      (SELECT quarter,amount FROM sale)s
      PIVOT
      (SUM(s.amount) FOR quarter
      IN ([spring],[summer],[autumn],[winter])
      )pTable

    • تشکر از مطلب مفیدتون.
      استفاده کردیم.

    • تشکر از مطلب مفیدتون.
      استفاده کردیم.

    • سلام و درود
      سرگذشت منم تا دبیرستان همین بود و عشق به کامپیوتر و اون یه واحد باعث شد برم ریاضی فیزیک .
      اونجا ما رو با بیسیک اشنا کردن و منم مثل شما دل تو دلم نبود چطوری یادش بگیرم .
      به قول خودت اینترنت که نبود ولی
      محل زندگی ما هییییییچ کلاسی هم نبود .
      خلاصه هیچی نشدیم .
      موفق باشی
      اموزشت عالی بود به دردم خورد.

    • سلام و درود
      سرگذشت منم تا دبیرستان همین بود و عشق به کامپیوتر و اون یه واحد باعث شد برم ریاضی فیزیک .
      اونجا ما رو با بیسیک اشنا کردن و منم مثل شما دل تو دلم نبود چطوری یادش بگیرم .
      به قول خودت اینترنت که نبود ولی
      محل زندگی ما هییییییچ کلاسی هم نبود .
      خلاصه هیچی نشدیم .
      موفق باشی
      اموزشت عالی بود به دردم خورد.

    • سلام، ممنون از آموزش خوبتون
      مشکلی که من دارم اینه که به جای ستون amount در جدول مثال، ستونی دارم که نوع داده ای اون nvarchar هستش و به همین دلیل نمیتونم از توابع جمعی استفاده کنم. میشه لطفا راهنماییم کنین که چجوری میتونم این کار رو انجام بدم؟
      باتشکر

      • با سلام
        اگر جنس دیتای nvarchar شما عددی باشد
        می توانید آن را CAST یا CONVERT کنید به INT یا BIGINT بعد از توابع تجمیعی استفاده کنید.

        تشکر از همراهی شما

    • سلام، ممنون از آموزش خوبتون
      مشکلی که من دارم اینه که به جای ستون amount در جدول مثال، ستونی دارم که نوع داده ای اون nvarchar هستش و به همین دلیل نمیتونم از توابع جمعی استفاده کنم. میشه لطفا راهنماییم کنین که چجوری میتونم این کار رو انجام بدم؟
      باتشکر

      • با سلام
        اگر جنس دیتای nvarchar شما عددی باشد
        می توانید آن را CAST یا CONVERT کنید به INT یا BIGINT بعد از توابع تجمیعی استفاده کنید.
        تشکر از همراهی شما

    • باسلام
      بسیار عالی.سپاسگزار

    • باسلام
      بسیار عالی.سپاسگزار

    • سلام بسیار ممنون برای مقاله بسیار خوبتون
      من از قابلیت pivot در پروسیجر استفاده کردم و وقتی میخواهم توی پروژه ام اضافه کنم میگه نوعش رو نمیشناسه
      از linq استفاده میکنم ممنون میشم اگه راهنمایی کنید
      تشکر

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
جشنواره عیدآموز نیک آموز، سال جدید رو با قدرت شروع کن
مشاهده تخفیف ها
close-image