جابجائی رکوردها و فیلدها همانند تصویر ضمیمه

[atf1379]

سلام
میخواهم مقادیر جدول1 رو همانند تصویر ضمیمه در یک کوئری قرار بدهم . آیا چنین امکانی وجود دارد ؟
بنظرم رسیدکه شاید با کوئری کراس تب انجام بشه ولی موفق نشدم
ممنون

[mazoolagh]

سلام و روز خوش

پرسش خوبی مطرح کردین،
اینکه باید از crosstab query استفاده کنین هم درسته ولی قبلش باید دیتا رو تغییرشکل بدین و مستقیما از روی دیتا اصلی نمیشه.
من با دیتا نمونه دیگه ای روش کار رو یادتون میدم.

[mazoolagh]

فرض کنین که دیتا ما جدول نمرات زیر باشه:
1.png

[mazoolagh]

اول یک کوئری میسازیم که دیتا رو برای کوئری کراس تب آماده کنه:

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

[mazoolagh]

حالا روی این کوئری 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

[mazoolagh]

و نتیجه کار:

3.png

[mazoolagh]

در MSSQL کار به این راحتی نیست چون در اکسس TRANSFORM رو داریم که اونجا نیست.
فرصت کنم نمونه MSSQL رو هم برای مقایسه میگذارم.

[amirzazadeh]

با سلام و احترام

[atf1379]

در MSSQL کار به این راحتی نیست چون در اکسس TRANSFORM رو داریم که اونجا نیست.
فرصت کنم نمونه MSSQL رو هم برای مقایسه میگذارم.

ممنون از لطفتون استاد!

[mazoolagh]

برای مقایسه و تکمیل بحث، روش کار در 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

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

[mazoolagh]

حالا کد 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

[mazoolagh]

از مقایسه این دو کد چند مورد مشخص میشه:

1- دستور TRANSFORM رو نداریم!
به همین خاطر مجبوریم تمام STUDENTها رو در بخش PIVOT بیاریم.
این خیلی کار رو پیچیده میکنه بخصوص اگر تعداد و اسامی STUDENTها تغییر کنه - که معمولا همیشه همین وضعیت هست.

2- شکل PIVOT بکلی متفاوت هست.

[mazoolagh]

برای ساخت خودکار سری اسامی از تکنیکی بنام 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

[mazoolagh]

مابقی کار راحت هست:

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)

[mazoolagh]

نتیجه کار در محیط MSSMS :

7.png

[mazoolagh]

یک تاپیک دیگه هست که در اونجا هم پس از تکمیل بحث اکسس، روش کار در MSSQL هم آمده و از تابع RANK برای رتبه بندی استفاده شده :
رتبه بندی عملکرد در گزارش (barnamenevis.org)

[mazoolagh]

با سلام و احترام

با سلام و احترام متقابل
شما همیشه لطف داشتین جناب میرزازاده

[mazoolagh]

ممنون از لطفتون !

ممنون از توجه و پیگیری شما،
به وعده وفا شد.

[atf1379]

ممنون از توجه و پیگیری شما،
به وعده وفا شد.

بسیار سپاسگزارم جناب استاد mazoolagh عزیز !