CrossTab یا Pivot

[ali_ahr7]

سلام.ببخشيد اگر عنوان سوال نامناسبه.چطور ميشه دستور select با خروجي زير نوشت؟

سال: 1388 1389
-----
موضوع
فعاليت
:
اجتماعي 4 6
فرهنگي 5 2
هنري 3 1

راستي اعداد بالا تعداد هر موضوغ توي هر سال هستند.
دوستان پيدا كردن جواب سوال بالا براي من خيلي ضروريه.متشكرم از توجهتون

[s.Jabbari]

میشه بگید شما این قرمت خروجی رو برای چی لازم دارید؟

[ali_ahr7]

سلام اين فرمت خروجي يك جدوله كه اطلاعات رو بر حسب دو پارامتر نمايش ميده.مثلا در سال 88 تعداد فعاليت هاي فرهنگي 5 بوده.متشكرم از توجهتون

[محمد سلیم آبادی]

سلام.ببخشيد اگر عنوان سوال نامناسبه.چطور ميشه دستور select با خروجي زير نوشت؟

سال: 1388 1389
-----
موضوع
فعاليت
:
اجتماعي 4 6
فرهنگي 5 2
هنري 3 1

راستي اعداد بالا تعداد هر موضوغ توي هر سال هستند.
دوستان پيدا كردن جواب سوال بالا براي من خيلي ضروريه.متشكرم از توجهتون

سلام،
منظورتان برایم کاملا روشن است. ولی راجب شمای/ساختار جدول دومتان (که همان جدولی اصلی است) اطلاعاتی به ما نداده این.

به هر حال بایستی جدول دوم شامل ستون های زیر باشد:
ستون سال (که Years در نظر گرفتم)
ستون موضوع (Subjects)
و ...

Query مورد نظر شما به شکل زیر در خواهد آمد.


SELECT Subjects,
SUM(CASE WHEN Years = 1388 THEN 1 ELSE 0 END) AS [1388],
SUM(CASE WHEN Years = 1389 THEN 1 ELSE 0 END) AS [1389]
FROM table_2
GROUP BY Subjects;


توضیح مختصر و مفید:
ابتدا بایستی بر اساس موضوعات گروه بندی کنین. سپس با کمک تابع SUM و عبارت CASE تعداد موضوعات در دو سال مورد نظر را برای هر موضوع بدست می آوریم.

[محمد سلیم آبادی]

میشه بگید شما این قرمت خروجی رو برای چی لازم دارید؟

سلام،
در حقیقت به این نوع گزارش گیری ها که خیلی معروف هم هست Cross Tab گفته می شود. که حتی در محصولات Office مثل EXCEL و ACCESS امکانی برای PivotTable وجود دارد.

مقاله ای در این موضوع نوشتم که بحث Index Tuning آن باقی مانده است. امید وارم بتوانم در آینده ی نه چندان دور آن را با کیفیت هرچه تمام تر در اینترنت انتشار دهم.

[ali_ahr7]

دوستان سلام.من با دستور بالا تونستم يك خروجي با يك پارامتر (سال تاسيس) بگيرم و هنوز نتونستم فعاليت ها رو به اون مرتبط كنم.ساختار جدول هم به اين صورته كه جدول اول شامل سال تاسيس و كليد اصلي و جدول دوم شامل موضوع فعاليت و كليد خارجي هست.

[محمد سلیم آبادی]

دوستان سلام.من با دستور بالا تونستم يك خروجي با يك پارامتر (سال تاسيس) بگيرم و هنوز نتونستم فعاليت ها رو به اون مرتبط كنم.ساختار جدول هم به اين صورته كه جدول اول شامل سال تاسيس و كليد اصلي و جدول دوم شامل موضوع فعاليت و كليد خارجي هست.

سلام،
من تصور کردم که "سال تاسیس" در جدول دوم وجود داره ولی اینطور که گفتین مجبوریم یک INNER JOIN بین این دو جدول ایجاد کنیم تا بتونیم به سال تاسیس دسترسی پیدا کنیم.

Query شما به این شکل خواهد بود:


SELECT Subject,
[1388] = SUM(CASE WHEN t1.year = 1388 THEN 1 ELSE 0 END),
[1389] = SUM(CASE WHEN t1.year = 1389 THEN 1 ELSE 0 END)
FROM table_1 AS t1
INNER JOIN table_2 AS t2
ON t1.PK = t2.FK
GROUP BY t2.Subject;

[ali_ahr7]

دوست عزيز جناب msalim اول از همه از شما بخاطر توجهتون متشكرم. و فقط يك سوال باقي ميمونه:
فرض كنيد من بدست آوردن امار اين دوسال آمار سال هاي 60 تا 89 يا حتي بعد از اون(هر چقدر كه كاربر اطلاعات وارد كرده باشد) را لازم دارم.اون موقع بايد چكار كنم؟
آيا يايد يكي يكي اين سطر ها را نوشت؟

و در آخر اينكه چرا از تابع sum استفاده كردين؟
مثلا چرا از تابع count استفاده نكردين؟
منتظر جوابتون هستم.بازهم متشكرم

[محمد سلیم آبادی]

آيا يايد يكي يكي اين سطر ها را نوشت؟
بله.
کاری را که سعی در انجامش دارین CrossTab نام داره که اگر از SQL Server 2005 و بالاتر استفاده می کنین برای راحتر شدن کار (مثلا برای هر سال نیازی به نوشتن یک سطر وجود نداره و در کل یک سطر برای مشخص کردن سال ها استفاده میشه) می تونین از عملگر غیر استاندارد جدولی PIVOT استفاده کنین.

اما از لحاظ سرعت هیچ توفیقی با روشی که از CASE استفاده شده نداره و روش CASE قابل استفاده در تمام نسخه های SQL Server هم هست چون از Syntax استاندارد استفاده شده.
از طرفی اگر سرعت اجرا برایتان در اولویت اول قرار دارد روشی هست که تعداد خطوطش چندین برابر query که نوشتم هست ولی سرعت اجرا شدندش بیشتر از همه ی روشهاست.

در آخر اينكه چرا از تابع sum استفاده كردين؟
مثلا چرا از تابع count استفاده نكردين؟

هیچ فرقی نداره ولی اگر بخواهین از COUNT به جای SUM استفاده کنین کد مربوطه تقریبا به این شکل درخواهند آمد

COUNT(CASE WHEN year = 1389 THEN 1 ELSE NULL END) --d

به کلمه ای که با رنگ قرمز مشخص کردم توجه کنین.
که بعد از اجرای query فوق در Text Result پیغام Warning زیر ظاهر خواهد شد البته این فقط یک پیغام هشدار دهنده است نه چیز دیگری:

Warning: Null value is eliminated by an aggregate or other SET operation

[ali_ahr7]

بله.
کاری را که سعی در انجامش دارین CrossTab نام داره که اگر از SQL Server 2005 و بالاتر استفاده می کنین برای راحتر شدن کار (مثلا برای هر سال نیازی به نوشتن یک سطر وجود نداره و در کل یک سطر برای مشخص کردن سال ها استفاده میشه) می تونین از عملگر غیر استاندارد جدولی PIVOT استفاده کنین.


دوست عزيز من چند تا مطلب درياره چيزي كه گفتين خوندم ولي متاسفانه چيزي دستگيرم نشد.به هر حال من از sqlserver 2000 استفاده ميكنم و متشكر ميشم اگه راهي رو براي موضوغ بالا ارائه بديد.بازم از توجهتون متشكرم.

[محمد سلیم آبادی]

من از sqlserver 2000 استفاده ميكنم


فرض كنيد من بدست آوردن امار اين دوسال آمار سال هاي 60 تا 89 يا حتي بعد از اون(هر چقدر كه كاربر اطلاعات وارد كرده باشد) را لازم دارم.اون موقع بايد چكار كنم؟

روش ها و راه حل هایی که من برای بدست آوردن این گزارش آماری رایج سراغ دارم (در SQL Server 2000) همگی Static (ایستا) هستند. ولی همانطور که فرمودین شما نیاز به یک روش پویا (Dynamic) دارین. واقعیت اینه که تا حالا با این موضوع درگیر نشدم. ولی مقاله هایی رو می شناستم مثلا:
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
یا اینکه:
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

فکر می کنم حتی برای کسی که چند ماه با SQL Server کار کرده باشه درکش سخت باشه و نیاز به صرف زمان و تمرکز باشه.

[ali_ahr7]

سلام الگوريتمي كه به ذهنم ميرسه اينه كه اول با يه select كوچكترين و بزرگترين سال رو از جدول ميگيريم و با يه حلقه عمليات رو انجام ميديم.من امتحان نكردم.به نظر شما شدنيه؟

[محمد سلیم آبادی]

در صورتی که سال ها دقیقا پشت سرهم باشند امکانش هست منظوم اینه که سالی بین min و max جانیوفتاده باشه.
توضیح اینکه بایستی سطرهای مربوط به CASE رو داخل یک حلقه تولید کنید سپس به کوئری اصلی الحاق کنین و با کمک دستور exec اجراش کنین.

تیکه ای از کد تقریبا باید به این شکل زیر دربیاد البته همینطوری نوشتم ممکنه اشتباهات زیادی داشته باشه. در چند روز آینده اگر فرصت بشه حتما یک راه حل براش طراحی می کنم و در این تاپیک قرار میدم.


DECLARE @command VARCHAR(1000),
@ start INT, @end INT;
DECLARE @command = ''
SELECT @start = MIN(year), @end = MAX(year)
FROM table_1;

WHILE @start <= @end
BEGIN
SET @Command += 'SUM(CASE WHEN year = ' + @start + ' THEN 1 ELSE 0 END)'
SET @start +=1;
END

[ali_ahr7]

دوست خوبمد جناب msalim باز هم متشكرم از توجه و اينكه وقتتون رو صرف كردين.من تونستم از مقاله هايي كه شما بالا گذاشتيد راه حل رو پيدا كنم.در صورت تمايل امر بفرماييد تا اونو تو همين صفحه بگذارم.بازم از لطفتون ممنونم.