سلام
میخواهم مقادیر جدول1 رو همانند تصویر ضمیمه در یک کوئری قرار بدهم . آیا چنین امکانی وجود دارد ؟
بنظرم رسیدکه شاید با کوئری کراس تب انجام بشه ولی موفق نشدم
ممنون
سلام
میخواهم مقادیر جدول1 رو همانند تصویر ضمیمه در یک کوئری قرار بدهم . آیا چنین امکانی وجود دارد ؟
بنظرم رسیدکه شاید با کوئری کراس تب انجام بشه ولی موفق نشدم
ممنون
سلام و روز خوش
پرسش خوبی مطرح کردین،
اینکه باید از crosstab query استفاده کنین هم درسته ولی قبلش باید دیتا رو تغییرشکل بدین و مستقیما از روی دیتا اصلی نمیشه.
من با دیتا نمونه دیگه ای روش کار رو یادتون میدم.
فرض کنین که دیتا ما جدول نمرات زیر باشه:
1.png
اول یک کوئری میسازیم که دیتا رو برای کوئری کراس تب آماده کنه:
SELECT Student , "Algebra" AS Course , Algebra AS Score FROM Grades
UNION
SELECT Student , "Arts" AS Course , Arts AS Score FROM Grades
UNION
SELECT Student , "Math" AS Course , Math AS Score FROM Grades
UNION
SELECT Student , "Physics" AS Course , Physics AS Score FROM Grades
حالا روی این کوئری Grades_Normalized کوئری کراس تب رو میسازیم:
4.png
TRANSFORM First(Grades_Normalized.Score) AS FirstOfScore
SELECT Grades_Normalized.Course
FROM Grades_Normalized
GROUP BY Grades_Normalized.Course
PIVOT Grades_Normalized.Student
و نتیجه کار:
3.png
در MSSQL کار به این راحتی نیست چون در اکسس TRANSFORM رو داریم که اونجا نیست.
فرصت کنم نمونه MSSQL رو هم برای مقایسه میگذارم.
با سلام و احترام
برای مقایسه و تکمیل بحث، روش کار در MSSQL رو هم میذارم.
قبلش توضیح این مطلب ضروری هست که:
اینجا ابتدا یک کوئری جدید برای تغییر شکل دیتا درست کردیم (Grades_Normalized) و روی اون PIVOT گرفتیم، چون نتیجه ای که میخواستیم مستقیما از دیتا اولیه بدست نمیومد.
این ساخت کوئری جدید فقط برای تقسیم کار به گام های کوچکتر و فهم بهتر مراحل کار هست، وگرنه همون اول هم میتونستیم این کوئری رو در همون دل کوئری PIVOT هم بگذاریم:
TRANSFORM First(Score) AS FirstOfScore
SELECT Course
FROM (
SELECT Student , "Algebra" AS Course , Algebra AS Score FROM Grades
UNION
SELECT Student , "Arts" AS Course , Arts AS Score FROM Grades
UNION
SELECT Student , "Math" AS Course , Math AS Score FROM Grades
UNION
SELECT Student , "Physics" AS Course , Physics AS Score FROM Grades) AS Grades_Normalized
GROUP BY Course
PIVOT Student
در هر صورت در نظر داشته باشین که این موضوع سلیقه ای هست و تا حد ممکن باید کدها و ابجکت ها رو ساده نگه داشت مگر اینکه در نتیجه و پرفورمنس و این دست موارد تاثیر داشته باشه.
خلاصه اینکه پیچیده بودن کدها نشونه برنامه نویسی بهتر نیست بخصوص از دید پشتیبانی و نگهداری.
حالا کد TSQL همین مسئله در MSSQL اگر بخوایم بنویسیم اینجوری هست:
SELECT * FROM (
SELECT [Student] , 'Algebra' AS Course , [Algebra] AS Score FROM Grades
UNION
SELECT [Student] , 'Arts' AS Course , Arts AS Score FROM Grades
UNION
SELECT [Student] , 'Math' AS Course , Math AS Score FROM Grades
UNION
SELECT [Student] , 'Physics' AS Course , Physics AS Score FROM Grades) AS INPUT
PIVOT (MIN([Score]) FOR [Student] IN ([Marilie],[Leatha],[Gilbert],[Harvey],[Heaney])) AS OUTPUT
5.png
از مقایسه این دو کد چند مورد مشخص میشه:
1- دستور TRANSFORM رو نداریم!
به همین خاطر مجبوریم تمام STUDENTها رو در بخش PIVOT بیاریم.
این خیلی کار رو پیچیده میکنه بخصوص اگر تعداد و اسامی STUDENTها تغییر کنه - که معمولا همیشه همین وضعیت هست.
2- شکل PIVOT بکلی متفاوت هست.
برای ساخت خودکار سری اسامی از تکنیکی بنام DYNAMIC SQL استفاده میکنیم،
به این صورت که کد TSQL رو بصورت یک STRING میسازیم و این استرینگ رو بعنوان Command-String با دستور Execute اجرا میکنیم.
قبل از اون باید مجموعه اسامی STUDENT ها رو بعنوان یک لیست جدا شده با کاما که هر کدوم درون [] قرار دارن بسازیم:
DECLARE @X NVARCHAR(MAX)
SELECT @X= STRING_AGG (QUOTENAME([STUDENT]) , ',') FROM Grades
SELECT @X AS ALL_STUDENTS
6.png
مابقی کار راحت هست:
DECLARE @X NVARCHAR(MAX)
SELECT @X= STRING_AGG (QUOTENAME([STUDENT]) , ',') FROM Grades
DECLARE @QRY NVARCHAR(MAX)= N'
SELECT * FROM (
SELECT [Student] , ''Algebra'' AS Course , [Algebra] AS Score FROM Grades
UNION
SELECT [Student] , ''Arts'' AS Course , Arts AS Score FROM Grades
UNION
SELECT [Student] , ''Math'' AS Course , Math AS Score FROM Grades
UNION
SELECT [Student] , ''Physics'' AS Course , Physics AS Score FROM Grades) AS INPUT
PIVOT (MIN([Score]) FOR [Student] IN (' + @X + ')) AS OUTPUT'
EXECUTE (@QRY)
نتیجه کار در محیط MSSMS :
7.png
یک تاپیک دیگه هست که در اونجا هم پس از تکمیل بحث اکسس، روش کار در MSSQL هم آمده و از تابع RANK برای رتبه بندی استفاده شده :
رتبه بندی عملکرد در گزارش (barnamenevis.org)