فرض کنید جدولی با نام sale و مطابق با چنین ساختاری داریم. این جدول شامل اطلاعات تعداد فروش (amount) بر اساس سال (year) و به ازای هر فصل (quarter) است.
حال می خواهیم به عنوان مثال، اطلاعات فروش هر سال را به تفکیک هر فصل داشته باشیم.
برای این کار از Aggregation Functionها استفاده کرده و اسکریپت زیر را اجرا می کنیم:
[sql]
SELECT year,quarter,SUM(amount) AS amountSum
FROM sale
GROUP BY YEAR,quarter
ORDER BY year
GO
[/sql]
خروجی کوئری بالا، اطلاعات فروش هر سال را به تفکیک هر فصل و در قالب یک رکورد نمایش می‌دهد.
در ادامه اگر بخواهیم اطلاعات فروش به ازای هر سال و بر اساس تمامی فصل¬ها صرفا در قالب یک رکورد یا یک سطر نمایش داده شود، باید چه کار کنیم؟
با استفاده از Sub Queryها این کار امکان‌پذیر است!
[sql]
SELECT DISTINCT year
,(SELECT SUM(amount)FROM sale s2 WHERE s2.year=s1.year AND s2.quarter=’spring’) AS spring
FROM sale s1
GO
[/sql]
به خروجی کوئری اجرا شده توجه کنید! این کوئری، صرفا جهت نمایش اطلاعات فروش فصل بهار است. بنابراین برای نمایش اطلاعات سایر فصل ها، می‌بایست آنها را در کوئری شرکت داد:
[sql]
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
[/sql]
همان طور که می‌بینید، توانستیم اطلاعات فروش در هر سال و به تفکیک هر فصل را در قالب یک رکورد نمایش دهیم اما نکته قابل تامل این است که اگر تنوع بازه زمانی اطلاعات فروش بر اساس ماه های مختلف در نظر گرفته شده بود آن گاه می‌بایست تمامی ماه های سال را در کوئری شرکت می‌دادیم!
این موضوع در خصوص موجودیت هایی متنوع، قطعا چالش برانگیز خواهد بود و روش بهینه ای به حساب نمی‌آید.
اکنون برای رفع این مشکل چه باید کرد؟ پاسخ SQL Server استفاده از PIVOT Tableها است.
PIVOT Table چیست؟
همان طور که در شکل پایین می‌بینید، خواسته ما، چرخش مقادیر داده ها از درون ستون های جدول به سمت Header گزارش است و این قابلیت به کمک PIVOT Tableها در SQL Server تامین میشود. به عبارت دیگر زمانی از PIVOT Tableها استفاده می‌کنیم که بخواهیم گزارش هایی از نوع Cross-Tab داشته باشیم.
الگوی استفاده از PIVOT Tableها در SQL به شکل زیر می‌باشد:
[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>;
[/sql]
بنابراین در ابتدای کار می‌بایست تکلیف سه مورد زیر را مشخص کنیم:
1- Aggregate Column: همان فیلدی است که قرار است بر روی آن عملیات Aggregation انجام شود که در مثال فرضی ما، فیلد amount خواهد بود.
2- PIVOT Column: فیلدی که قرار است از درون رکوردها به سمت Header گزارش چرخش داشته باشد که در مثال فرضی ما، فیلد quarter خواهد بود. این فیلد در جلو عبارت FOR قرار می‌گیرد.
3- لیستی که قرار است گزارش براساس آن تهیه شود که در مثال فرضی ما، مقادیر فیلد quarter خواهد بود که همان spring,summer,autumn,winter خواهند بود.
اکنون با تشخیص موارد بالا، همه چیز برای ایجاد کوئری فراهم شده است:
[sql]
SELECT * FROM sale
PIVOT
(SUM (amount) FOR quarter
IN ([spring],[summer],[autumn],[winter]))pTable
[/sql]
شکل زیر مقایسه میان Plan اجرایی این کوئری (استفاده از PIVOT) و کوئری قبلی (استفاده از Sub Query) را نشان می‌دهد و شما می‌بینید که به لحاظ کارآیی، استفاده از PIVOT Tableها به چه میزان تاثیر گذار خواهند بود. مقایسه دیاگرام Plan اجرایی این دو کوئری هم در نوع خودش جالب توجه است. از طرفی میزان خطوط نوشته شده در هر کوئری هم جای تامل دارد!
ضمنا باید به این نکته هم توجه داشته باشید که با استفاده از ایندکس گذاری مناسب، قطعا می‌توانیم به کارآیی بیشتر این گونه کوئری ها کمک کنیم.
در ادمه می‌خواهیم تغییراتی بر روی جدول sale اعمال کنیم. این تغییرات شامل افزودن یک فیلد از نوع INT و با خصوصیت IDENTITY است:
[sql]
ALTER TABLE sale
ADD id INT IDENTITY
[/sql]
مجددا همان کوئری ای را که در آن از PIVOT استفاده شده بود، اجرا می‌کنیم.
خروجی کوئری، مطابق با آنچه که ما انتظارش را داشتیم، نیست!
آیا می‌توان چنین استنباط کرد که قابلیت PIVOT صرفا برای جداول سه فیلدی ایجاد شده است؟ پاسخ مثبت و چنین برداشتی، قطعا موجب رنجش خاطر تیم توسعه دهنده Microsoft SQL Server خواهد شد!
اما بیایید با هم بررسی کنیم که چرا چنین اتفاقی افتاده و راه برون رفت از آن چیست؟
دوباره به کوئری زیر توجه کنید. فرض می‌کنیم هنوز به جدول مان فیلد id را اضافه نکرده ایم. کوئری زیر را اجرا می‌کنیم:
[sql]
SELECT * FROM sale
PIVOT
(SUM (amount) FOR quarter
IN ([spring],[summer],[autumn],[winter]))pTable
[/sql]
در این کوئری، SQL نتایج را بر اساس سال فروش (year) تفکیک کرده است. اما SQL از کجا تشخیص داده است که باید چنین کاری را انجام بدهد؟ پاسخ آن است که در این حالت تمامی فیلد های یک جدول به غیر از Aggregate Column و PIVOT Column، توسط SQL در GROUP BY شرکت داده می‌شوند که در این جا شامل فیلد year میشود.
این موضوع در Plan اجرایی کوئری، به وضوح قابل مشاهده است.
البته این قاعده در برخی از موارد به ضرر ما تمام میشود و این همان جایی است که مثلا به جدول sale یک فیلد id اضافه شود. آن گاه علاوه بر فیلد year، فیلد id هم در GROUP BY شرکت داده میشود و نتایج مورد انتظارمان حاصل نخواهد شد.
برای رفع چنین مشکلی می‌بایست به جای استفاده از SELECT ای مستقیم از جدول sale، با استفاده از یک Sub Query، فیلدهای موردنظرمان را در SELECT انتخاب کنیم.
اسکریپت زیر، نحوه انجام کار را به شما نشان می‌دهد:
[sql]
SELECT * FROM
(SELECT year,quarter,amount FROM sale)s
PIVOT
(SUM(s.amount) FOR quarter
IN ([spring],[summer],[autumn],[winter])
)pTable
[/sql]
و اما حالا شما به عنوان تمرین، تلاش کنید کوئری ای بنویسید که خروجی زیر را نمایش دهد:
امیدوارم این مقاله برای شما سودمند باشد.
در قسمت بعدی مقاله، شما را با چالش جدیدی در حوزه PIVOT روبرو خواهم کرد.
منبع: آموزش SQL Server
19 دیدگاه
مرتضی
با سلام واحترام
محمد علی صحرانورد صحرانورد
با سلام
خیلی عالی بود
داود وحدانی
با سلام
حسن ضرابی
با سلام و خسته نباشید
از مقاله خیلی خوبتان بسیار ممنونم خیلی خوب و عالی بود
علی اکبر شعبانی
خیلی خوب بود مقالتون ممنونم
Homa hedarpour
خیلی خیلی منمونم ،بسیا رسا بود.
mobtaker
سلام استاد ، وقت بخیر
محسن
خیلی خوب بود ، تشکر
ab
سلام
مهدی شیشه بری شیشه بری
سلام
لطفا قسمت دوم مقاله را مطالعه کنید. پاسخ تان را آن جا پیدا خواهید کرد.
شاد باشید.
ab
سلام خواندم اما متاسفانه جوابم را بدست نیاوردم
درضمن تعداد داده های ستون name متغییر است وتعداد ثابتی ندارد
چگونه من باید کوئری بنویسم تا به جواب مورد نظرم برسم
اگر درست نمایش داده شود چنین چیزی می خوام
Date a b c d e۲۰۱۶-۰۵-۰۱ ۱۰۰ ۱۰۳ ۱۰۶ ۱۰۹ ۱۱۲۲۰۱۶-۰۵-۰۲ ۱۰۱ ۱۰۴ ۱۰۷ ۱۱۰ ۱۱۳۲۰۱۶-۰۵-۰۳ ۱۰۲ ۱۰۵ ۱۰۸ ۱۱۱ ۱۱۴باتشکر
مهدی شیشه بری شیشه بری
سلام
امیدوارم منظورتان را درست متوجه شده باشم.
لطفا اسکریپت زیر را اجرا کنید و در صورت عدم حصول نتیجه، اسکریپت خودتان را این جا بگذارید تا ببینم ایراد کار از کجاست.
خوب و خوش باشید.
SET @Name=”
SELECT @Name=@Name+Name+’,’ FROM test3
GROUP BY Name
SET @Name=LEFT(@Name,LEN(@Name)-1)
EXEC(‘SELECT * FROM
(SELECT Date,Name,Price FROM test3)t
PIVOT
(SUM(Price) FOR Name
IN (‘+@Name+’))pTable’)
ab
باسلام و سپاس از جواب خوبتان
privatevoidForm1_Load(objectsender, EventArgs e){DataClasses1DataContext dc =newDataClasses1DataContext();var staff = dc.aaaa();radGridView1.DataSource = staff;}جواد پهلوان
سلام بسیار ممنون برای مقاله بسیار خوبتون
من از قابلیت pivot در پروسیجر استفاده کردم و وقتی میخواهم توی پروژه ام اضافه کنم میگه نوعش رو نمیشناسه
از linq استفاده میکنم ممنون میشم اگه راهنمایی کنید
تشکر
هادی سعیدی
باسلام
بسیار عالی.سپاسگزار
prvsh
سلام، ممنون از آموزش خوبتون
مشکلی که من دارم اینه که به جای ستون amount در جدول مثال، ستونی دارم که نوع داده ای اون nvarchar هستش و به همین دلیل نمیتونم از توابع جمعی استفاده کنم. میشه لطفا راهنماییم کنین که چجوری میتونم این کار رو انجام بدم؟
باتشکر
آرزو محمدزاده
با سلام
اگر جنس دیتای nvarchar شما عددی باشد
می توانید آن را CAST یا CONVERT کنید به INT یا BIGINT بعد از توابع تجمیعی استفاده کنید.
تشکر از همراهی شما
مصیب
سلام و درود
سرگذشت منم تا دبیرستان همین بود و عشق به کامپیوتر و اون یه واحد باعث شد برم ریاضی فیزیک .
اونجا ما رو با بیسیک اشنا کردن و منم مثل شما دل تو دلم نبود چطوری یادش بگیرم .
به قول خودت اینترنت که نبود ولی
محل زندگی ما هییییییچ کلاسی هم نبود .
خلاصه هیچی نشدیم .
موفق باشی
اموزشت عالی بود به دردم خورد.
میلاد حاتمی
تشکر از مطلب مفیدتون.
استفاده کردیم.