مشکل در بازیابی اطلاعات جدول

[skysky]

سوال: چه طور می توانم اطلاعات ستون نمره از جدول Grade را به درس های مختلف تفکیک کنم و نمره هر درس را در ستون خودش نشان دهم در واقع چه طور میتوان اطلاعات یک فیلد در جدول sql را تفکیک کرده و در ستون های مختلف Gridview در asp نمایش داد؟ مثل شکل زیر


فیزیک زبان ادبیات
19 12 18
15 14 16
16 20 17
اگه فقط راهنمایی کنید که دستورش رو چه طور بنویسم خودم در asp استفاده میکنم. خیلی ممنون
توضیحات: 4 جدول به نام های course,teacher,grade,student داریم که جدولGrade از طریق کلیدهای خارجی با 3 جدول دیگر در ارتباطه ولی در اینجا نتونستم ارتباطشون رو براتون نشان بدم. جدول course اطلاعات درس را نشان میدهد.

شماره دانشجوییstNo
نمره درسGrade
شماره درسCourseNo



جدول Course
Courseno (کلید صلی)
Name (نام درس)


جدولTeacher
Teachercode(کلید اصلی)
Fname
Lname



جدولGrade
(کلید خارجی) stno
Term
Courseno(کلید خارجی)
Grade
Teachercode(کلید خارجی)



جدولstudent
Stnoکلید اصلی
Fname
Lname
Id1
classname


این کد رو باید در برنامه Asp و با زبان C#‎‎‎‎ استفاده کنم .باید شماره دانشجو دریافت و نمراتش نمایش داده شود.

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

در واقع شما نیاز به مبحثی به نام Pivoting Data دارین. اگر با کمک کلید واژه ی PIVOT در هیمن سایت جستجو کنین چند تاپیک پیدا می کنین.

اینو امتحان کنین:


select stNo, Term,
MAX(case when CourseNo = 1 then Grade end), --فیزیک
MAX(case when CourseNo = 2 then Grade end), -- زبان
MAX(case when CourseNo = 3 then Grade end) -- ادبیات
from Grade
group by stNo, Term

[skysky]

ظاهرا امکان استفاده از pivot فقط در sql2005 وجود داره ولی من این کار رو میخوام در sql2000انجام بدم!

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

ظاهرا امکان استفاده از pivot فقط در sql2005 وجود داره ولی من این کار رو میخوام در sql2000انجام بدم!

Query که برایتان نوشته و ارسال کردم در حقیقت کار Pivot را انجام میده. قبل از معرفی Pivot این روشی بود که همه استفاده می کردن (یعنی استفاده از تابع Case همراه با توابع تجمعی)

[skysky]

با تشکر فراوان از پاسخی که دادید. یه کم در مورد کد توضیح میدید؟ من تا حالا از case در sql استفاده نکردم. چه طوری میگیم برای ما نمره رو برگردون در صورتی که جلوی stNo, select مینویسیم؟ کدی که دادید اجرا کردم ولی فقط یک سطر از اطلاعات درس ها رو نمایش میده یعنی اگه مثلا برای در فیزیک دو تا نمره داشته باشیم فقط یک نمره رو نمایش میده! ؟؟؟؟این کد رو میخوام به صورت stored prosedure در asp با زبان C#‎ بنویسم ولی نمدونم چه طور پارامتر رو براش ارسال کنم؟ ارسال ÷ارامتر در اس کیو ال میدونم ولی در asp نه!

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

ببینید من با استفاده از دو ستون StNo و Term گروه بندی کردم. پس باید غیر ممکنه باشه که در یک ترم یک دانشجو دو بار درس فیزیک رو انتخاب کرده.
هسته ی گزارش گیری مربوط به جدول Grade میشه که برای بدست آوردن اطلاعات بیشتر حاصل این query را با دیگر جداول Join می کنیم.

در مورد تابع case در sql. (http://barnamenevis.org/forum/showthread.php?t=171586)

؟؟؟؟این کد رو میخوام به صورت stored prosedure در asp با زبان C#‎‎ بنویسم ولی نمدونم چه طور پارامتر رو براش ارسال کنم؟ ارسال ÷ارامتر در اس کیو ال میدونم ولی در asp نه!

این سوال را می تونین در بخش asp مطرح کنین

[skysky]

از اینکه به جواب سوالم دادید خیلی ممنون. من از Join و یک دستور دیگه هم استفاده کرده( جلوی select از Grade استفاده کردم ) و هر دو یک جواب داد و کل نمرات فرد رو برام نشون میده به صورت شکل زیر


فیزیک زبا ن Grade
19null19
12Null12
18Null18
. ولی من نمیخوام ستون Grade نمایش داده بشه اما مجبورم Grade رو جلوی select بنویسم ( چون نمره روبروی group by اومده باید روبروی select هم بنویسم) راهی هست که بتونم نشونش ندم و Nullها رو هم حذف کنم؟ در ضمن در هر دو روش در حالتی که پروسیجر نیستند جواب بالا را میدند اما وقتی به صورت پروسیجر مینویسم از خط آخر (having) ایراد می گیره.چرا؟ خیلی ممنون از لطفتون


create procedure karname1 @stNo intcreate procedure karname1 @stNo int
As
select Grade,
MAX(case when g1.CourseNo = 1 then g1.Grade end), --فیزیک
MAX(case when g1.CourseNo = 2 then g1.Grade end), -- زبان
MAX(case when g1.CourseNo = 3 then g1.Grade end) -- ادبیات
from Grade g1 INNER join Grade g2 ON g1.stNo=g2.stNo
group by g1.Grade
having g1.stNo=@stNo
-----------------
Server: Msg 8121, Level 16, State 1, Procedure karname1, Line 3
Column 'g1.stNo' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
روش دوم:
create procedure karname1 @stNo int
As
select Grade,
MAX(case when CourseNo = 1 then Grade end), --فیزیک
MAX(case when CourseNo = 2 then Grade end), -- زبان
MAX(case when CourseNo = 3 then Grade end) -- ادبیات
from Grade
group by Grade
having stNo=@stNo
خطا:
Server: Msg 8121, Level 16, State 1, Procedure karname1, Line 3
Column 'Grade.stNo' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

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

فیزیک زبان ادبیات
19 12 18
15 14 16
16 20 17

دوست خوبم، نتیجه ی فوق واقعا به چه معناست؟ آیا نمرات مربوط به یک نفر است؟ آیا یک نفر یک درس را چند بار انتخاب می کند؟ آیا بر اساس ترم و کددانشجو گروه بندی شده؟
اگر ستون Grade مربوط به نمره اخذ شده است پس چرا شما در کوئری پست قبل بر اساس ستون نمره ی هر درس گروه بندی کردین؟
آیا کد درس با مقدار 1 به درس فیزیک مربوط میشه؟
وقتی می خواهین روی ستونی که بر اساس آن گروه بندی نشده در ماده ی Having شرط بزارین حتما باید از توابع تجمعی مثل Max استفاده کنین.

[skysky]

سوال دقیقا اینه که کاربر شماره دانش آموزی یا همون stNo رو وارد کنه و بر اساس آن نمرات خودش رو در طول ترم مشاهده کنه حالا ممکنه یک دانش آموز در طول ترم چند تا نمره در یک درس داشته باشهو من باید همه آنها رو براش نمایش بدم چون باید شماره دانش آموزی دریافت بشه می خوام به صورت پروسیجر بنویسم و شماره دانش آموزی پارامتر اون باشه.
مثل اینکه می خواد کارنامه بگیره.بله کد درس فیزیک 1،درس زبان 2 است.
میخوام Database رو براتون بفرستم ولی هر چی روی دکمه ضمیمه کلیک می کنم جواب نمیده!
همون طور که فرمودید من از تابع Max در Having استفاده کردم ولی خطا میده:

having Max(stNo=@stNo)
Server: Msg 170, Level 15, State 1, Procedure karname1, Line 12
='.Line 12: Incorrect syntax near '

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

having max(stNo)=@stNo
اینطوری کار پیش نمیره، حتما باید کد و داده های جدول Grade رو به یک شکلی ارسال کنین. از برنامه ی Microsoft SQL Server Database Publishing Wizard برای گرفتن Script از کد و داده هایتان استفاده کنین و آن را داخل تگ کد قرار بدین.

[skysky]

واقعا از لطفتون ممنونم. من دیتابیس براتون به آدرس زیر میل کردم االان کاملش رو ندارم ولی کاملش بعدا براتون می فرستم
mohammad.salimabadi@gmail

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

جدول Grade اصلا هیچ کلید اولیه ای نداره! این باعث ناهنجاری شده و حتی زبان SQL ام از پس نوشتن query روی این داده ها بر نخواهد آمد.
به سوال زیر بدقت جواب بدین:
اگر St# ستون مربوط به کد دانشجو باشه، و Crs# ستون مربوط به کد درس باشه و Mark ستون مربوط به نمره درس باشه و سطرهای زیر را در جدول Grade تصور کنیم:


/*
St# Crs# Mark
----------- ----------- -----------
1001 1 15
1001 2 14
1001 6 9
1001 1 17
*/

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


/*St# 1 2 3
----------- ----------- ----------- -----------
1001 15 14 NULL
1001 17 NULL NULL
*/

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

اگر جواب سوال پست قبلی مثبت است، از sp زیر استفاده کنین:


use Hesabi
go

create procedure Pivoting (@stNo nvarchar(10)) as
begin
declare @s nvarchar(100)
set @s=('use tempdb
If OBJECT_ID(''##t'',''U'') is not null drop table ##t')

exec(@s)

select *, R=identity(int, 1, 1)
into ##t
from dbo.Grade
where stNo = @stNo
and Term = 1

select MAX(case when d.courseNo=1 then Grade end) as [1],
MAX(case when d.courseNo=2 then Grade end) as [2],
MAX(case when d.courseNo=3 then Grade end) as [3]
from (select *, row_id= (select count(*)
from ##t as t1
where t1.courseNo=t2.courseNo
and t1.r <= t2.r)
from ##t as t2) as d
group by d.row_id
end

go
exec Pivoting '150454851'

/*
1 2 3
------------- ------------- -------------
15 17 15
12 5 13
NULL 18 NULL
*/

[skysky]

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

If OBJECT_ID(''##t'',''U'') is not null drop table ##t')

exec(@s)

select *, R=identity(int, 1, 1)


میشه به جای اعداد 1و2و3 در ستون ها اسم درس ها بیاد؟ من اعداد رو به اسم درس ها تغییر دادم ولی هیچ اتفاقی نیفتاد

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

چون شما از ورژن 2000 استفاده می کنین و جدول شما هیچ ستون identity نداشت من مجبور شدم برای دادن یک مقدار identity از تابع identity into استفاده کنم.
این تابع نتیجه را در داخل یک جدول موقت ذخیره می کن. اگر بار دوم شما SP را اجرا کنین از آنجا که قبلا جدول ساخته شده است، سعی در ساخت مجدد آن با پیغام خطا روبرو خواهد شد برای جلوگیری از این اتفاق قبل از ساخت اگر وجود داشته باشد حذف می شود....

نام ستون های را به راحتی از 1و2و3 به هر نامی میشود تغییر دارد.