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

[atf1379]

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

[mazoolagh]

سلام و روز خوش

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

[mazoolagh]

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

[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 کوئری کراس تب رو میسازیم:


153894
TRANSFORM First(Grades_Normalized.Score) AS FirstOfScore
SELECT Grades_Normalized.Course
FROM Grades_Normalized
GROUP BY Grades_Normalized.Course
PIVOT Grades_Normalized.Student

[mazoolagh]

و نتیجه کار:

153895

[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

153949

[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

153950

[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 :

153951

[mazoolagh]

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

[mazoolagh]

با سلام و احترام
:تشویق::تشویق::تشویق::تشویق:

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

[mazoolagh]

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

[atf1379]

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

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