Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Create RowNumber and Rank in Sql queries

Author  Topic 

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-06 : 20:26:17
Dear Sir or Madam

First,I have the problem relevant to sql queries is that I want to put automatically RowNumber when I enter data in sql server or insert statement.I search internet then they show using
Select ROW_NUMBER() OVER(ORDER BY StudentID DESC) AS rownum,FullName
But I make error ('ROW_NUMBER' is not a recognized function name.')

Can you help me?

Second,I want to create rank in sql query depending on the average value.The wanted result is showed the following statement:"

rowNum--------FullName-------StudentID------Average-----Rank
1-------------seyha----------7777-----------50.15-------5
2-------------chan-----------5554-----------40.56-------6
3-------------heng-----------5474-----------70.12-------1
4-------------than-----------1245-----------40.56-------6

How Can I do to calculate Rank by Average
I look forward to hearing from you
Thank you in advance
Good Luck and Suceed your work or study


seyha moth

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2006-12-06 : 21:19:00
Row_Number() is a function that was introduced in sql server 2005 so unless you have that it won't work for you. Also that will not be stored with the data but rather derived as when the rows are selected. What you described sounds like you want an IDENTITY column. Read about that in Books Online.

as for the rank you can do something like this:
select <columnList>, Rank = (select count(*) from yourTable as b where b.[Average] >= a.[Average])
from yourTable as a

EDIT:
I my be missing something...why do your rank values skip so many numbers?

Be One with the Optimizer
TG
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-06 : 22:15:58
Thank u for your reply,but I am using SQL Server 2005,so RowNumber still unrecognize this command.As If Average is compute column from Score,but I forget to input score to you.Now I will copy entire source code to you.

ALTER Procedure [dbo].[procX_ScoreResult]
AS
declare @EnSubjectName nvarchar(50)
declare @Str nvarchar(4000)
declare subject_cursor CURSOR
FOR Select distinct EnSubjectName from vX_ScoreWithStudentInfo where AcademicYear='2006-2007' and Year='3' and Semester=2
OPEN subject_cursor
set @Str=''
FETCH NEXT from subject_cursor into @EnSubjectName

WHILE @@FETCH_STATUS=0
BEGIN
set @Str=@Str+'['+@EnSubjectName+']'+'=ISNULL(Sum(CASE WHEN a.EnSubjectName='''+@EnSubjectName+''' THEN a.Score END),0),'
FETCH NEXT FROM subject_cursor into @EnSubjectName
END
CLOSE subject_cursor
set @Str=Substring(@Str,0,Len(@Str))
--Exec('Select FullName,Sex=Case WHEN Sex=1 THEN ''M'' WHEN Sex=2 THEN ''F'' END,StudentID,'+ @Str +',Round(Avg(Score),2) AS Average, From vX_ScoreWithStudentInfo Group by FullName,Sex,StudentID')
Exec('Select FullName,Sex=Case WHEN Sex=1 THEN ''M'' WHEN Sex=2 THEN ''F'' END,StudentID,'+ @Str +',Round(Avg(Score),2) AS Average, From vX_ScoreWithStudentInfo Group by FullName,Sex,StudentID')
DEALLOCATE subject_cursor




seyha moth
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-12-07 : 00:05:31

1 If you use front end application, do numbering there
2 Use temp table with identity column

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-07 : 04:06:16
Thank u for your reply.May be I can solve this problem with your help.
But I can't solve Rank because Average is compute column and If I compare like this

select <columnList>, Rank = (select count(*) from yourTable as b where b.[Average] >= a.[Average])
from yourTable as a

If and only of Average is normal column

How Can I do?
Good Luck and Suceed your work or study



seyha moth
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-12-07 : 04:10:28
quote:
Originally posted by seyha_moth

rowNum--------FullName-------StudentID------Average-----Rank
1-------------seyha----------7777-----------50.15-------5
2-------------chan-----------5554-----------40.56-------6
3-------------heng-----------5474-----------70.12-------1
4-------------than-----------1245-----------40.56-------6
Yes, this is your wanted result, but how do your base data look like?
Please post the sample data who lead to the result above.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-07 : 04:58:59
FullName Sex StudentID C Program vb.net asp.net java average rank
San Kim Leang F 20040002 0 0 0 -2 -2 1
Vann Voleak F 20040005 0 0 0 -2 -2 1
Phourng F 20040010 0 0 0 -2 -2 1
Vorn Bunna Rith M 20040015 0 -2 0 -2 -1.33 3
Som Suy M 20040036 0 0 -2 0 -2 1
Meas Varun F 20040065 0 0 0 -2 -2 1
Buth Van Deth M 20040081 0 0 -2 0 -2 1
Un Rattana F 20040138 0 0 0 -2 -2 1
Ly Chenda M 20040157 0 -2 0 -2 -1.33 3
Mao BoRey F 20040175 0 0 0 -2 -2 1
Vann Srey Pao F 20040176 0 0 0 -2 -2 1
Sim Sothy F 20040180 0 0 0 -2 -2 1
Heng Thea Ny F 20040181 0 0 0 -2 -2 1
Koy Sophea Ra M 20040184 0 0 0 -2 -2 1
Sor So Ny F 20040185 0 0 0 -2 -2 1
Cheak Tey F 20040186 0 0 0 -2 -2 1
Say Sova Thana F 20040188 0 0 0 -2 -2 1
Nhip SokKhen M 20040190 0 -2 0 -2 -1.33 3
Than ffff M 20040193 0 -2 0 -2 -1.33 3
Seng Pisey M 20040194 0 -2 0 -2 -1.33 3
Meas Phearom M 20040211 0 0 -2 0 -2 1
Ly Panga F 20040215 0 0 -2 0 -2 1
Tav Dany F 20040230 0 0 0 -2 -2 1
Chhin Bean M 20040232 0 0 -2 0 -2 1
Kol Sovan Mon M 20040236 0 0 -2 0 -2 1
Kou Ku Naet F 20040239 0 0 -2 0 -2 1
Bun Sina F 20040269 0 0 0 -2 -2 1
Ou Sopheak Na F 20040270 0 -2 0 -2 -1.33 3
Nal NiMol F 20040274 0 0 0 -2 -2 1
Meth Ma Liden F 20040276 0 0 0 -2 -2 1
Ponh Long Dy M 20040277 0 -2 0 -2 -1.33 3
Huot SomBath M 20040278 0 0 0 -2 -2 1
Morn Many F 20040301 0 0 0 -2 -2 1
Cheang Puthea Rath F 20040307 0 0 0 -2 -2 1
Leng Srey Mom F 20040309 0 0 0 -2 -2 1
Kou Bakke M 20040358 0 -2 0 -2 -1.33 3
SamBath Samnang F 20040399 0 0 -2 0 -2 1
Chum Puthy Roath M 20040400 0 0 0 -2 -2 1
Sun Da Mean M 20040420 0 0 -2 0 -2 1
Phon Chan Meardey F 20040451 0 0 0 -2 -2 1
Hong Phary F 20040480 0 0 0 -2 -2 1
Khek Bunna M 20040483 0 0 0 -2 -2 1
Thel rattana M 20040518 0 -2 0 -2 -1.33 3
Doung ChamRoe M 20040520 0 0 0 -2 -2 1
Serey ma len F 20040527 0 0 0 -2 -2 1
Lee Hak Sim M 20040529 0 0 0 -2 -2 1
SENG CHAN DA M 20040558 0 0 -2 0 -2 1
Som So Pheak M 20040559 0 0 -2 0 -2 1
Ek Vu Tha M 20040560 0 0 -2 0 -2 1
Ear Chheng Hai M 20040583 0 0 0 -2 -2 1
Phon Sotheary F 20040601 0 0 0 -2 -2 1
Prach Sela F 20040602 0 0 0 -2 -2 1
Hak Sophearkna F 20040606 0 0 0 -2 -2 1
En he lene F 20040609 0 0 0 -2 -2 1
An sophat M 20040611 35 -2 0 -2 10.33 3
Kin sa roeun F 20040644 0 0 0 -2 -2 1
Phal pisith M 20040678 0 0 -2 0 -2 1
Tang muoy ngim F 20040686 0 0 0 -2 -2 1
Khin veasna M 20040706 0 -2 0 -2 -1.33 3
Buoy Da ly F 20040743 0 0 -2 0 -2 1
Cheng Di Na F 20040751 0 0 -2 0 -2 1
Ros SamBo M 20040757 0 -2 0 -2 -1.33 3
Seang Ratha M 20040760 0 0 -2 0 -2 1
Tang lay chay M 20040790 0 0 0 -2 -2 1
Ou kim horn M 20040793 0 -2 0 -2 -1.33 3
Sem chak riya F 20040799 0 0 0 -2 -2 1
Sok sara voeun M 20040802 0 -2 0 -2 -1.33 3
Thuch di na F 20040826 0 0 0 -2 -2 1
Pin dina F 20040829 0 0 0 -2 -2 1
Horth leakhena F 20040830 0 0 0 -2 -2 1
Samrith so F 20040831 0 0 0 -2 -2 1
Eng khunnary F 20040832 0 -2 0 -2 -1.33 3
Kim so phal F 20040834 0 0 0 -2 -2 1
Hak pov M 20040842 0 0 -2 0 -2 1
Eang sok bopha F 20040851 0 0 -2 0 -2 1
Pok Makara F 20040855 0 0 0 -2 -2 1
Srin serey M 20040863 0 0 0 -2 -2 1
Seng tan M 20040889 0 0 -2 0 -2 1
Choup So Mony F 20040928 0 0 0 -2 -2 1
Run veasna F 20040933 0 0 0 -2 -2 1
Real Chan Thea M 20040952 0 0 0 0 -2 1
Seng mar dy F 20040954 0 -2 0 -2 -1.33 3
Prum Phyrun M 20041000 0 -2 0 -2 -1.33 3
Nget Cheakny F 20041002 0 0 0 -2 -2 1
Soeung Mora F 20041059 0 0 0 -2 -2 1
Heng Thida F 20041060 0 0 0 -2 -2 1
Sok Visal F 20041062 0 0 0 -2 -2 1
Ngo SiChan F 20041064 0 0 0 -2 -2 1
Vong Kek Sokorn M 20041068 0 -2 0 -2 -1.33 3
Chhuon Hok Seng M 20041072 0 0 0 -2 -2 1
Chhun so nath F 20041085 0 0 -2 0 -2 1
Kim seen M 20041089 0 0 -2 0 -2 1
Kob k soem M 20041095 0 0 -2 0 -2 1
Leang so pheap F 20041123 0 0 0 -2 -2 1
Bun vat M 20041140 0 0 0 -2 -2 1
Ouk so rida F 20041172 0 -2 0 -2 -1.33 3
Hul sam nang M 20041177 0 0 -2 0 -2 1
Morn chan leak F 20041193 0 0 -2 0 -2 1
Sar srey neang F 20041199 0 0 0 -2 -2 1
Nong sokna F 20041201 0 0 0 -2 -2 1
Pech sam phors F 20041207 0 0 0 -2 -2 1
Tiw Sok Leng F 20041232 0 0 0 -2 -2 1
Sek Phanet M 20041241 0 -2 0 -2 -1.33 3
Koh Kanhara F 20041263 0 0 0 -2 -2 1
Eam Makara F 20041294 0 0 0 -2 -2 1
Som Sa ra M 20041305 0 -2 0 -2 -1.33 3
Khun Rachana M 20041306 0 -2 0 -2 -1.33 3
Seng Bo ka F 20041309 0 0 0 -2 -2 1
An Chenda F 20041325 0 0 -2 0 -2 1
Roeun Lina M 20041347 0 0 0 0 -2 1
Thy Sokha F 20041349 0 0 0 -2 -2 1
Touch Kakda F 20041355 0 -2 0 -2 -1.33 3
Choup Sakkna F 20041357 0 0 0 -2 -2 1
No Theary F 20041388 0 0 0 -2 -2 1
Ry sok chan M 20041429 0 -2 0 -2 -1.33 3
Bun malis F 20041430 0 0 0 -2 -2 1
Sok seila M 20041446 0 0 0 -2 -2 1
Pith mary neth F 20041471 0 0 0 -2 -2 1
Soeung amra F 20041513 0 0 0 -2 -2 1
Oum sophorn F 20041545 0 0 0 -2 -2 1
Krouch siphan M 20041548 0 0 -2 0 -2 1
El Many F 20041630 0 0 -2 0 -2 1
Min Chan F 20041641 0 0 0 -2 -2 1
Phom Chan F 20041667 0 0 0 -2 -2 1
Chan LiNeth F 20041692 0 0 0 -2 -2 1
Im Narom F 20041701 0 0 0 -2 -2 1
Ngeab Chheng M 20041726 0 0 -2 0 -2 1
Oum Srey Mom F 20041750 0 0 -2 0 -2 1
Pech Heng M 20041762 0 0 -2 0 -2 1
Khun Sokun The F 20041770 0 0 0 -2 -2 1
Ly Rachana F 20041814 0 0 0 -2 -2 1
Chab Thearin F 20041853 0 0 0 -2 -2 1
Eun Sok Chea M 20041854 0 0 -2 0 -2 1
Ham Sok Teang F 20041883 0 0 0 -2 -2 1
Heng Navy F 20041892 0 0 0 -2 -2 1
Pak Kap M 20041896 0 -2 0 -2 -1.33 3
Kao Tay Hok M 20042086 0 0 -2 0 -2 1
Huot Borin M 20042105 0 0 0 -2 -2 2
Keo Sokha M 20050106 0 0 0 -2 -2 2
Man Sitha F 20050141 0 0 0 0 -2 1
Sao So Vorn M 20050170 0 0 0 0 -2 1
Long Daneth F 20050317 0 0 0 -2 -2 2
Oeung Naro F 20050494 0 0 0 -2 -2 1
Chan Mengly M 20050609 0 0 0 0 19 1
Chan Sreymex F 20050652 0 0 0 0 29 1
Hong Kim Sea F 20050705 0 0 0 0 -2 1
Nou Chamroeun M 20050735 0 0 0 0 -2 1
Chhoeun M 20050748 0 0 0 0 9 1
Meng Sophearon M 20050893 0 0 0 0 -2 1
Touch Pisakha M 20050922 0 0 0 0 -2 1
Sovath Seyha M 20050953 0 0 0 0 -2 1
Moung Samnang M 20050992 0 0 0 0 -2 1
Din Si Deth M 20051009 0 0 0 0 -2 1
Ty Oudam M 20051021 0 0 0 0 -2 1
Hun Vatheany F 20051059 0 0 0 0 -2 1
Pos So Pha M 20051075 0 0 0 0 -2 1
Huot Siha Vorin M 20051078 0 0 0 0 -2 1
Sum Chhor Woan F 20051087 0 0 0 0 -2 1
Meas Sophan N M 20051120 0 0 0 0 -2 1
Khun Choeurn M 20051149 0 0 0 0 -2 1
Kim Si Na F 20051164 0 0 0 0 -2 1
Ma So Phea F 20051483 0 0 0 0 -2 1
Kim Piseth F 20051495 0 0 0 -2 -2 1
Chan dara M 20051746 0 0 0 0 19 1
Sngourn Sok Ch F 20051768 0 0 0 0 -2 1
Rang So Thea M 20051773 0 0 0 0 -2 1
Kong Chamroeun M 20051790 0 0 0 0 -2 1
Pich Veasna M 20051899 0 0 0 0 -2 1
Lor Kim Sreang M 20051905 0 0 0 0 -2 1
Ngorn Rathna F 20052091 0 0 0 0 -2 1
Seng So Phakdey M 20052104 0 0 0 0 -2 1
Lin Sokha Lin F 20052333 0 0 0 0 -2 1
Sin Bun Chheang M 20052505 0 0 0 0 -2 1
Khun So Voleak F 20052623 0 0 0 0 -2 1
Heng Theara M 20052775 0 0 0 0 -2 1
Thang Ly Hor M 20052809 0 0 0 0 -2 1
Long Sokha F 20052881 0 0 0 0 -2 1
Kheang Sok Leap M 20052945 0 0 0 0 -2 1
Savann Sophea M 20060057 0 0 -2 0 -2 1
Poeu Vuthy M 20060149 0 0 0 0 -2 3
Yin Somolina F 20060153 0 0 0 0 -2 3
Chee Leang Heng M 20060208 0 0 0 0 -2 3
Ben Bunly M 20060224 0 0 0 0 -2 3
ENG ONTOUCH M 20060227 0 0 0 0 -2 3
CHHUON MONORA M 20060232 0 0 0 0 -2 3
Srong Samnong M 20060273 0 0 0 0 -2 3
VA SOKLEANG F 20060286 0 0 0 0 -2 3
Khorn Khanya F 20060290 0 0 0 0 -2 3
Keo Sotheary M 20060296 0 0 0 0 -2 3
Ros Salin M 20060308 0 0 0 0 -2 3
Thou Chaktodara F 20060323 0 0 0 0 -2 3
Sin Son Vatey F 20060351 0 0 0 0 -2 3
Som Tola M 20060364 0 0 0 0 -2 3
Viseth Pech M 20060384 0 0 0 0 -2 3
Chhorn Ron M 20060389 0 0 0 0 -2 3
In Reasey F 20060391 0 0 0 0 -2 3
Hem Sarin Danou F 20060398 0 0 0 0 -2 3
Morm Leakhena F 20060415 0 0 0 0 -2 3
Ny Malyka M 20060418 0 0 0 0 -2 3
Lun Peng Srorn M 20060419 0 0 0 0 -2 3
Tho Sopheanith M 20060440 0 0 0 0 -2 3
Roeung Naro M 20060451 0 0 0 0 -2 3
Ray Sreymoch F 20060455 0 0 0 0 -2 3
Lim Hak M 20060470 0 0 0 0 -2 3
Srey Sovatey F 20060478 0 0 0 0 -2 3
Sok So Maline F 20060480 0 0 0 0 -2 3
Phauk Satya M 20060481 0 0 0 0 -2 3
Thay Meng Heang M 20060488 0 0 0 0 -2 3
Touch Vantha F 20060494 0 0 0 0 -2 3
Por Vibol M 20060495 0 0 0 0 -2 3
Ul Sophon M 20060497 0 0 0 0 -2 3
Te Sokuntheary F 20060503 0 0 0 0 -2 3
You Leang M 20060508 0 0 0 0 -2 3
Thun Sokunthea F 20060512 0 0 0 0 -2 3
Yem Vibon M 20060520 0 0 0 0 -2 3
Cheav Hak M 20060525 0 0 0 0 -1.67 3
An Sokly F 20060547 30 60 0 -2 29.33 3
Lim Bun Hong M 20060551 0 0 0 0 -2 3
Ngoun Somaly F 20060552 0 0 0 0 -2 3
So Phak Ny F 20060555 0 0 0 0 -2 3
Soun Sophal F 20060562 0 0 0 0 -2 3
Choun dfdsf F 20060563 0 0 0 0 11.67 3
Chea Pisey M 20060576 0 0 0 0 -1 3
Kim Ratha M 20060577 0 0 0 0 -2 3
Sroy Eak Heng M 20060583 0 0 0 0 -2 3
Sam Chamnap M 20060604 0 0 0 0 -2 3
Nham Chamroeun M 20060613 0 0 0 0 -2 3
Som Panchakmor F 20060633 0 0 0 0 -2 3
Heang Danine F 20060635 0 0 0 0 -2 3
Thoul Thavona F 20060638 0 0 0 0 -2 3
Sieng Bun Ny M 20060639 0 0 0 0 -2 3
Som Sophal F 20060646 0 0 0 0 -2 3
Kheav Chita F 20060650 0 0 0 0 -2 3
Auth Sichann F 20060656 0 0 0 0 5 3
Nouv Saronn F 20060657 0 0 0 0 -2 3
LY Serey Vuth M 20060683 0 0 0 0 -2 3
Ty Sokun Virya F 20060684 0 0 0 0 -2 3
Lim Sreyleak F 20061007 0 0 0 0 -2 3
Seng Rachana F 20061008 0 0 0 0 -2 3
Mao Pisey F 20061253 0 0 0 0 -2 3
Chhit Chamnan M 20061269 0 0 0 0 -2 3
Mao Solina F 20061444 0 0 0 0 -2 3
Din Sophea M 20061506 0 0 0 0 -2 3
Som Molika F 20061680 0 0 0 0 -2 3
Leng Da M 20061695 0 0 0 0 -2 3
Choum Chan F 20061756 0 0 0 0 -2 3
Uy Dalune F 20061782 0 0 0 0 -2 3
Chea Sambath M 20061792 0 0 0 0 -2 3
Sok Heng M 20061884 0 0 0 0 -2 3
Tha Sreymom F 20062445 0 0 0 0 -2 3
Tan Lida F 20062446 0 0 0 0 -2 3
Him Ratanak M 20062448 0 -2 0 -2 -1.33 3
Yuk Sengchann M 20062691 0 0 0 0 -2 3
Sam An Yden M 20062811 0 0 0 -2 -2 1
Sann Sary M 20062843 0 0 0 0 -2 3
Heng Savy M 21030010 0 0 0 -2 -2 1
Sok chak riya F 21030082 0 0 0 -2 -2 2
Uk Sengly F 21030108 0 0 0 -2 -2 2
khlaing Vutha M 21030109 0 0 0 -2 -2 1
Chhim soch F 21030116 0 0 0 -2 -2 1
Chan ra vy F 21030164 0 0 0 -2 -2 1
Prom Sophonn F 21030283 0 0 0 -2 -2 2
Phai ma kara M 22030005 0 0 0 -2 -2 2
Chhim Thida F 22030010 0 0 0 -2 -2 2
Kong Sotha Ry F 22030013 0 0 0 -2 -2 2
Srey Vanna F 22030018 0 0 0 -2 -2 2
Chea Sarath F 22030019 0 0 0 -2 18.5 2
Bun Pesey M 22030022 0 0 0 -2 3.5 2
Chay Maly F 22030024 0 0 0 -2 13.5 2
Ka Puthea rith M 22030040 0 0 0 -2 -2 2
Chhoeurn vann M 22030042 0 0 0 -2 -2 2
Chuk Seihak M 22030061 0 0 0 -2 -2 2
Ros so phea F 22030067 0 0 0 -2 -2 2
Chuon raneat F 22030078 0 0 0 -2 -2 2
Long Sochit M 22030080 0 0 0 -2 -2 2
Chheang so F 22030091 0 0 0 -2 -2 2
Chan vichet M 22030095 0 0 0 -2 18.5 2
Tep Neavea M 22030102 0 0 0 -2 -2 2
Ell rano F 22030104 0 0 0 -2 -2 2
Chin vira deth F 22030107 0 0 0 -2 -2 2
Chan dani da F 22030143 0 0 0 -2 8.5 2
Thlork chan F 22030153 0 0 0 -2 -2 2
Loy Leang Eng M 22030162 0 0 0 -2 -2 2
Morn phana M 25030160 0 -2 0 -2 -1.33 3
Ek Pholla M 28030008 0 0 0 10 3.33 3
ONG KARIN DET M 28030055 0 0 0 -2 -2 2
Chhay Seth M 29030366 0 0 0 -2 -2 1
Math Lima F 6966 0 0 0 -2 -2 2
Chea Davy F 7551 0 0 0 -2 -2 1
Leng Khuy M 7624 0 0 0 -2 -2 2
Chim van nak M 7689 0 0 -2 0 -2 1
Taing Pengny M 7724 0 0 0 -2 -2 2
Doung darareak M 8396 0 0 -2 0 -2 1
En roth F 8451 0 0 -2 0 -2 1
Pok Chanthan M 8727 0 0 0 -2 -2 2

seyha moth
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-07 : 05:03:56
Sorry I make complexity

seyha moth
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-07 : 05:31:12
This is the sample view(vX_ScoreWithStudentInfo)
FullName-----Sex-------StudentID----EnSubjectName------Score
seyha--------M---------7444-----------Vb.net-------20
seyha--------M---------7444-----------java---------50
seyha--------M---------7444-----------Sql server---70
chan---------M---------4578-----------vb.net-------50
chan---------M---------4578-----------java---------60
chan---------M---------4578-----------Sql server---50

I create cross tab using cross tab queries

CREATE Procedure [dbo].[procX_ScoreResult]
AS
declare @EnSubjectName nvarchar(50)
declare @Str nvarchar(4000)
declare subject_cursor CURSOR
FOR Select distinct EnSubjectName from vX_ScoreWithStudentInfo
OPEN subject_cursor
set @Str=''
FETCH NEXT from subject_cursor into @EnSubjectName

WHILE @@FETCH_STATUS=0
BEGIN

set @Str=@Str+'['+@EnSubjectName+']'+'=ISNULL(Sum(CASE WHEN EnSubjectName='''+@EnSubjectName+''' THEN Score END),0),'

FETCH NEXT FROM subject_cursor into @EnSubjectName
END
CLOSE subject_cursor
set @Str=Substring(@Str,0,Len(@Str))

Exec('Select FullName,Sex=Case WHEN Sex=1 THEN ''M'' WHEN Sex=2 THEN ''F'' END,StudentID,'+ @Str +',Round(Avg(Score),2) AS Average From vX_ScoreWithStudentInfo Group by FullName,Sex,StudentID')

DEALLOCATE subject_cursor

Afterward,I want to get the following result:

No---FullName-------Sex--------vb.net-----java-------Sql Server----Average----Rank
1----seyha----------M----------20---------50---------70------------46.67------2
2----chan-----------M----------50---------60---------50------------53.33------1

But almost of my problems is solved already,it remains Average,Rank,RowNumber(No) that I can calculate it.

Can u help me?
I look forward to hearing from you
Thank you in advance
Good Luck and Succeed your work and study.

seyha moth
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-12-07 : 05:35:15
Try this
-- prepare test data
DECLARE	@Test TABLE (FullName VARCHAR(21), Sex CHAR(1), StudentID INT, [C program] SMALLINT, [VB.Net] SMALLINT, [ASP.Net] SMALLINT, Java SMALLINT, Average SMALLMONEY, Rank SMALLINT)

INSERT	@Test
SELECT 'An Chenda', 'F', 20041325, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'An Sokly', 'F', 20060547, 30, 60, 0, -2, 29.33, 3 UNION ALL
SELECT 'An sophat', 'M', 20040611, 35, -2, 0, -2, 10.33, 3 UNION ALL
SELECT 'Auth Sichann', 'F', 20060656, 0, 0, 0, 0, 5, 3 UNION ALL
SELECT 'Ben Bunly', 'M', 20060224, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Bun malis', 'F', 20041430, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Bun Pesey', 'M', 22030022, 0, 0, 0, -2, 3.5, 2 UNION ALL
SELECT 'Bun Sina', 'F', 20040269, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Bun vat', 'M', 20041140, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Buoy Da ly', 'F', 20040743, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Buth Van Deth', 'M', 20040081, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Chab Thearin', 'F', 20041853, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Chan dani da', 'F', 22030143, 0, 0, 0, -2, 8.5, 2 UNION ALL
SELECT 'Chan dara', 'M', 20051746, 0, 0, 0, 0, 19, 1 UNION ALL
SELECT 'Chan LiNeth', 'F', 20041692, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Chan Mengly', 'M', 20050609, 0, 0, 0, 0, 19, 1 UNION ALL
SELECT 'Chan ra vy', 'F', 21030164, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Chan Sreymex', 'F', 20050652, 0, 0, 0, 0, 29, 1 UNION ALL
SELECT 'Chan vichet', 'M', 22030095, 0, 0, 0, -2, 18.5, 2 UNION ALL
SELECT 'Chay Maly', 'F', 22030024, 0, 0, 0, -2, 13.5, 2 UNION ALL
SELECT 'Chea Davy', 'F', 7551, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Chea Pisey', 'M', 20060576, 0, 0, 0, 0, -1, 3 UNION ALL
SELECT 'Chea Sambath', 'M', 20061792, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Chea Sarath', 'F', 22030019, 0, 0, 0, -2, 18.5, 2 UNION ALL
SELECT 'Cheak Tey', 'F', 20040186, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Cheang Puthea Rath', 'F', 20040307, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Cheav Hak', 'M', 20060525, 0, 0, 0, 0, -1.67, 3 UNION ALL
SELECT 'Chee Leang Heng', 'M', 20060208, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Cheng Di Na', 'F', 20040751, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Chhay Seth', 'M', 29030366, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Chheang so', 'F', 22030091, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Chhim soch', 'F', 21030116, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Chhim Thida', 'F', 22030010, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Chhin Bean', 'M', 20040232, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Chhit Chamnan', 'M', 20061269, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Chhoeun', 'M', 20050748, 0, 0, 0, 0, 9, 1 UNION ALL
SELECT 'Chhoeurn vann', 'M', 22030042, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Chhorn Ron', 'M', 20060389, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Chhun so nath', 'F', 20041085, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Chhuon Hok Seng', 'M', 20041072, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'CHHUON MONORA', 'M', 20060232, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Chim van nak', 'M', 7689, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Chin vira deth', 'F', 22030107, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Choum Chan', 'F', 20061756, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Choun dfdsf', 'F', 20060563, 0, 0, 0, 0, 11.67, 3 UNION ALL
SELECT 'Choup Sakkna', 'F', 20041357, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Choup So Mony', 'F', 20040928, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Chuk Seihak', 'M', 22030061, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Chum Puthy Roath', 'M', 20040400, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Chuon raneat', 'F', 22030078, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Din Si Deth', 'M', 20051009, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Din Sophea', 'M', 20061506, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Doung ChamRoe', 'M', 20040520, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Doung darareak', 'M', 8396, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Eam Makara', 'F', 20041294, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Eang sok bopha', 'F', 20040851, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Ear Chheng Hai', 'M', 20040583, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Ek Pholla', 'M', 28030008, 0, 0, 0, 10, 3.33, 3 UNION ALL
SELECT 'Ek Vu Tha', 'M', 20040560, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'El Many', 'F', 20041630, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Ell rano', 'F', 22030104, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'En he lene', 'F', 20040609, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'En roth', 'F', 8451, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Eng khunnary', 'F', 20040832, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'ENG ONTOUCH', 'M', 20060227, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Eun Sok Chea', 'M', 20041854, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Hak pov', 'M', 20040842, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Hak Sophearkna', 'F', 20040606, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Ham Sok Teang', 'F', 20041883, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Heang Danine', 'F', 20060635, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Hem Sarin Danou', 'F', 20060398, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Heng Navy', 'F', 20041892, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Heng Savy', 'M', 21030010, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Heng Thea Ny', 'F', 20040181, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Heng Theara', 'M', 20052775, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Heng Thida', 'F', 20041060, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Him Ratanak', 'M', 20062448, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Hong Kim Sea', 'F', 20050705, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Hong Phary', 'F', 20040480, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Horth leakhena', 'F', 20040830, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Hul sam nang', 'M', 20041177, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Hun Vatheany', 'F', 20051059, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Huot Borin', 'M', 20042105, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Huot Siha Vorin', 'M', 20051078, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Huot SomBath', 'M', 20040278, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Im Narom', 'F', 20041701, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'In Reasey', 'F', 20060391, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Ka Puthea rith', 'M', 22030040, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Kao Tay Hok', 'M', 20042086, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Keo Sokha', 'M', 20050106, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Keo Sotheary', 'M', 20060296, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Kheang Sok Leap', 'M', 20052945, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Kheav Chita', 'F', 20060650, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Khek Bunna', 'M', 20040483, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Khin veasna', 'M', 20040706, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'khlaing Vutha', 'M', 21030109, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Khorn Khanya', 'F', 20060290, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Khun Choeurn', 'M', 20051149, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Khun Rachana', 'M', 20041306, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Khun So Voleak', 'F', 20052623, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Khun Sokun The', 'F', 20041770, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Kim Piseth', 'F', 20051495, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Kim Ratha', 'M', 20060577, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Kim seen', 'M', 20041089, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Kim Si Na', 'F', 20051164, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Kim so phal', 'F', 20040834, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Kin sa roeun', 'F', 20040644, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Kob k soem', 'M', 20041095, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Koh Kanhara', 'F', 20041263, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Kol Sovan Mon', 'M', 20040236, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Kong Chamroeun', 'M', 20051790, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Kong Sotha Ry', 'F', 22030013, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Kou Bakke', 'M', 20040358, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Kou Ku Naet', 'F', 20040239, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Koy Sophea Ra', 'M', 20040184, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Krouch siphan', 'M', 20041548, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Leang so pheap', 'F', 20041123, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Lee Hak Sim', 'M', 20040529, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Leng Da', 'M', 20061695, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Leng Khuy', 'M', 7624, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Leng Srey Mom', 'F', 20040309, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Lim Bun Hong', 'M', 20060551, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Lim Hak', 'M', 20060470, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Lim Sreyleak', 'F', 20061007, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Lin Sokha Lin', 'F', 20052333, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Long Daneth', 'F', 20050317, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Long Sochit', 'M', 22030080, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Long Sokha', 'F', 20052881, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Lor Kim Sreang', 'M', 20051905, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Loy Leang Eng', 'M', 22030162, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Lun Peng Srorn', 'M', 20060419, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Ly Chenda', 'M', 20040157, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Ly Panga', 'F', 20040215, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Ly Rachana', 'F', 20041814, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'LY Serey Vuth', 'M', 20060683, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Ma So Phea', 'F', 20051483, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Man Sitha', 'F', 20050141, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Mao BoRey', 'F', 20040175, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Mao Pisey', 'F', 20061253, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Mao Solina', 'F', 20061444, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Math Lima', 'F', 6966, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Meas Phearom', 'M', 20040211, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Meas Sophan N', 'M', 20051120, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Meas Varun', 'F', 20040065, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Meng Sophearon', 'M', 20050893, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Meth Ma Liden', 'F', 20040276, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Min Chan', 'F', 20041641, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Morm Leakhena', 'F', 20060415, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Morn chan leak', 'F', 20041193, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Morn Many', 'F', 20040301, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Morn phana', 'M', 25030160, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Moung Samnang', 'M', 20050992, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Nal NiMol', 'F', 20040274, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Ngeab Chheng', 'M', 20041726, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Nget Cheakny', 'F', 20041002, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Ngo SiChan', 'F', 20041064, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Ngorn Rathna', 'F', 20052091, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Ngoun Somaly', 'F', 20060552, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Nham Chamroeun', 'M', 20060613, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Nhip SokKhen', 'M', 20040190, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'No Theary', 'F', 20041388, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Nong sokna', 'F', 20041201, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Nou Chamroeun', 'M', 20050735, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Nouv Saronn', 'F', 20060657, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Ny Malyka', 'M', 20060418, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Oeung Naro', 'F', 20050494, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'ONG KARIN DET', 'M', 28030055, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Ou kim horn', 'M', 20040793, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Ou Sopheak Na', 'F', 20040270, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Ouk so rida', 'F', 20041172, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Oum sophorn', 'F', 20041545, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Oum Srey Mom', 'F', 20041750, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Pak Kap', 'M', 20041896, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Pech Heng', 'M', 20041762, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Pech sam phors', 'F', 20041207, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Phai ma kara', 'M', 22030005, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Phal pisith', 'M', 20040678, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Phauk Satya', 'M', 20060481, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Phom Chan', 'F', 20041667, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Phon Chan Meardey', 'F', 20040451, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Phon Sotheary', 'F', 20040601, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Phourng', 'F', 20040010, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Pich Veasna', 'M', 20051899, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Pin dina', 'F', 20040829, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Pith mary neth', 'F', 20041471, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Poeu Vuthy', 'M', 20060149, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Pok Chanthan', 'M', 8727, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Pok Makara', 'F', 20040855, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Ponh Long Dy', 'M', 20040277, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Por Vibol', 'M', 20060495, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Pos So Pha', 'M', 20051075, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Prach Sela', 'F', 20040602, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Prom Sophonn', 'F', 21030283, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Prum Phyrun', 'M', 20041000, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Rang So Thea', 'M', 20051773, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Ray Sreymoch', 'F', 20060455, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Real Chan Thea', 'M', 20040952, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Roeun Lina', 'M', 20041347, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Roeung Naro', 'M', 20060451, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Ros Salin', 'M', 20060308, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Ros SamBo', 'M', 20040757, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Ros so phea', 'F', 22030067, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Run veasna', 'F', 20040933, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Ry sok chan', 'M', 20041429, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Sam An Yden', 'M', 20062811, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Sam Chamnap', 'M', 20060604, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'SamBath Samnang', 'F', 20040399, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Samrith so', 'F', 20040831, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'San Kim Leang', 'F', 20040002, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Sann Sary', 'M', 20062843, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Sao So Vorn', 'M', 20050170, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Sar srey neang', 'F', 20041199, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Savann Sophea', 'M', 20060057, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Say Sova Thana', 'F', 20040188, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Seang Ratha', 'M', 20040760, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Sek Phanet', 'M', 20041241, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Sem chak riya', 'F', 20040799, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Seng Bo ka', 'F', 20041309, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'SENG CHAN DA', 'M', 20040558, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Seng mar dy', 'F', 20040954, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Seng Pisey', 'M', 20040194, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Seng Rachana', 'F', 20061008, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Seng So Phakdey', 'M', 20052104, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Seng tan', 'M', 20040889, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Serey ma len', 'F', 20040527, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Sieng Bun Ny', 'M', 20060639, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Sim Sothy', 'F', 20040180, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Sin Bun Chheang', 'M', 20052505, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Sin Son Vatey', 'F', 20060351, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Sngourn Sok Ch', 'F', 20051768, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'So Phak Ny', 'F', 20060555, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Soeung amra', 'F', 20041513, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Soeung Mora', 'F', 20041059, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Sok chak riya', 'F', 21030082, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Sok Heng', 'M', 20061884, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Sok sara voeun', 'M', 20040802, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Sok seila', 'M', 20041446, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Sok So Maline', 'F', 20060480, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Sok Visal', 'F', 20041062, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Som Molika', 'F', 20061680, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Som Panchakmor', 'F', 20060633, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Som Sa ra', 'M', 20041305, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Som So Pheak', 'M', 20040559, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Som Sophal', 'F', 20060646, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Som Suy', 'M', 20040036, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Som Tola', 'M', 20060364, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Sor So Ny', 'F', 20040185, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Soun Sophal', 'F', 20060562, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Sovath Seyha', 'M', 20050953, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Srey Sovatey', 'F', 20060478, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Srey Vanna', 'F', 22030018, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Srin serey', 'M', 20040863, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Srong Samnong', 'M', 20060273, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Sroy Eak Heng', 'M', 20060583, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Sum Chhor Woan', 'F', 20051087, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Sun Da Mean', 'M', 20040420, 0, 0, -2, 0, -2, 1 UNION ALL
SELECT 'Taing Pengny', 'M', 7724, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Tan Lida', 'F', 20062446, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Tang lay chay', 'M', 20040790, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Tang muoy ngim', 'F', 20040686, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Tav Dany', 'F', 20040230, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Te Sokuntheary', 'F', 20060503, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Tep Neavea', 'M', 22030102, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Tha Sreymom', 'F', 20062445, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Than ffff', 'M', 20040193, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Thang Ly Hor', 'M', 20052809, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Thay Meng Heang', 'M', 20060488, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Thel rattana', 'M', 20040518, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Thlork chan', 'F', 22030153, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Tho Sopheanith', 'M', 20060440, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Thou Chaktodara', 'F', 20060323, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Thoul Thavona', 'F', 20060638, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Thuch di na', 'F', 20040826, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Thun Sokunthea', 'F', 20060512, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Thy Sokha', 'F', 20041349, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Tiw Sok Leng', 'F', 20041232, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Touch Kakda', 'F', 20041355, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Touch Pisakha', 'M', 20050922, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Touch Vantha', 'F', 20060494, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Ty Oudam', 'M', 20051021, 0, 0, 0, 0, -2, 1 UNION ALL
SELECT 'Ty Sokun Virya', 'F', 20060684, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Uk Sengly', 'F', 21030108, 0, 0, 0, -2, -2, 2 UNION ALL
SELECT 'Ul Sophon', 'M', 20060497, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Un Rattana', 'F', 20040138, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Uy Dalune', 'F', 20061782, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'VA SOKLEANG', 'F', 20060286, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Vann Srey Pao', 'F', 20040176, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Vann Voleak', 'F', 20040005, 0, 0, 0, -2, -2, 1 UNION ALL
SELECT 'Viseth Pech', 'M', 20060384, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Vong Kek Sokorn', 'M', 20041068, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Vorn Bunna Rith', 'M', 20040015, 0, -2, 0, -2, -1.33, 3 UNION ALL
SELECT 'Yem Vibon', 'M', 20060520, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Yin Somolina', 'F', 20060153, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'You Leang', 'M', 20060508, 0, 0, 0, 0, -2, 3 UNION ALL
SELECT 'Yuk Sengchann', 'M', 20062691, 0, 0, 0, 0, -2, 3

-- this is where your query begins
select		(select count(*) from @test x where x.studentid <= t.studentid) rowNum,
		t.FullName,
		t.Sex,
		t.StudentID,
		t.[C program],
		t.[VB.Net],
		t.[ASP.Net],
		t.Java,
		t.Average,
		t.Rank,
		(select count(distinct x.average) from @test x where x.average >= t.average) newRank
from		@test t
order by	t.studentid


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-07 : 05:44:33
If you do like this,column subject can't fix that is why vb.net,java,asp.net can have subject increase and decrease

seyha moth
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-12-07 : 05:52:14
POST THE CORRECT BASE DATA THEN !!!
Why should I waste more time on you when you don't provide the correct details?

Go read this article http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-12-07 : 06:15:51
[code]-- prepare test data
create table #test (FullName varchar(5), Sex char(1), StudentID smallint, EnSubjectName varchar(10), Score tinyint)

insert #test
select 'seyha', 'M', 4578, 'Vb.net', 20 union all
select 'seyha', 'M', 4578, 'java', 50 union all
select 'peso', 'M', 999, 'Vb.net', 90 union all
select 'peso', 'M', 999, 'java', 20 union all
select 'peso', 'M', 999, 'Sql server', 100 union all
select 'chan', 'M', 7444, 'java', 60 union all
select 'chan', 'M', 7444, 'Sql server', 50

-- this is your code
CREATE TABLE #Aggregates
(
RowText VARCHAR(50),
ColumnText VARCHAR(50),
CellData MONEY
)

INSERT INTO #Aggregates
(
RowText,
ColumnText,
CellData
)
SELECT StudentID,
EnSubjectName,
Score
FROM #Test

CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)

CREATE TABLE #Columns
(
ColumnIndex INT IDENTITY (0, 1),
ColumnText VARCHAR(50)
)

INSERT INTO #Columns
(
ColumnText
)
SELECT DISTINCT ColumnText
FROM #Aggregates (INDEX(IX_Aggregates), NOLOCK)
ORDER BY ColumnText

CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)

CREATE TABLE #Rows
(
RowText INT,
Average SMALLMONEY,
Rank INT,
rowNum INT
)

INSERT INTO #Rows
(
RowText
)

SELECT DISTINCT RowText
FROM #Aggregates (INDEX(IX_Aggregates), NOLOCK)

CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)

DECLARE @ColumnIndex INT,
@MaxColumnIndex INT,
@ColumnText VARCHAR(50),
@SQL VARCHAR(1000)

SELECT @ColumnIndex = 0,
@MaxColumnIndex = MAX(ColumnIndex)
FROM #Columns


WHILE @ColumnIndex <= @MaxColumnIndex
BEGIN
SELECT @ColumnText = ColumnText
FROM #Columns
WHERE ColumnIndex = @ColumnIndex

SELECT @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' INT'
EXEC (@SQL)

SELECT @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.CellData
FROM #Aggregates (INDEX(IX_Aggregates), NOLOCK)
, #Columns (INDEX(IX_Columns), NOLOCK)
WHERE #Rows.RowText = #Aggregates.RowText
AND #Columns.ColumnText = #Aggregates.ColumnText
AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
EXEC (@SQL)

SELECT @ColumnIndex = @ColumnIndex + 1
END

DROP TABLE #Columns
DROP TABLE #Aggregates

update r
set r.average = (select avg(score) from #test t where t.studentid = r.rowtext)
from #rows r

update r
set r.rownum = (select count(*) from #rows x where x.rowtext <= r.rowtext)
from #rows r

update r
set r.rank = (select count(*) from #rows x where x.average >= r.average)
from #rows r

SELECT t.fullname, t.studentid,
r.*
FROM #Rows r
inner join (select distinct studentid, fullname from #test) t on t.studentid = r.rowtext
ORDER BY r.RowText

DROP TABLE #Rows

drop table #test[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-08 : 02:24:06
Thank you for your reply and I'm sorry for my mistakes and I hope that you don't be mind especially help me to correct my mistakes.
According to your queries,I run it then It makes the wanted result,but I have more recrods in my tables,so I have to input

select 'seyha', 'M', 4578, 'Vb.net', 20 union all
select 'seyha', 'M', 4578, 'java', 50 union all
select 'peso', 'M', 999, 'Vb.net', 90 union all
select 'peso', 'M', 999, 'java', 20 union all
select 'peso', 'M', 999, 'Sql server', 100 union all
select 'chan', 'M', 7444, 'java', 60 union all
.
.
.
select 'chan', 'M', 7444, 'Sql server', 50

Until finish,moreover,It will be had more and more records that user input and update it.

Could you have more method to correct it?
For me,I want to create Cursor to do it

I look forward to hearing from you
Thank you in advance
Good Luck and Succeed your work.





seyha moth
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-12-08 : 08:12:36
No, you only take the code from the point where it says "This is your code".
Then you alter the names of columns and tables in this section
INSERT INTO #Aggregates
            (
             RowText,
             ColumnText,
             CellData
            )
SELECT      StudentID,
            EnSubjectName,
            Score
FROM        #Test
to get the data from YOUR tables and YOUR columns in YOUR environment.
Good luck!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-08 : 22:12:40
Thank you for your reply.I solve succesfully with your help

I hope that I can help if you have problems

Thank you.
Good Bye
Good Luck and Succeed what you want

seyha moth
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-08 : 22:50:19
Oh,Sorry Sir
I make the error when I change #test to vX_ScoreWithStudentInfo(this is the sample view that i told you already).It show that

(863 row(s) affected)
Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '25030040'.
The statement has been terminated.

According to my test,I think that it make error in line

CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)

How Can I do?
Can you help me?
I look forward to hearing from
Thank you in advance
Good Luck and Succeed your work.


seyha moth
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-09 : 04:19:12
Sorry,Sir I find the above problem by my self,but the its result show how to I don't want because the ordered column

It show:
FullName-------StudentID-------RowText-----Average---Rank---rowNum---Access----vb.net----java
I want
rowNum----FullName----Sex---StudentID---Access---vb.net---java---Average---Rank

I try find the solution with it,but I think that you can help me

I look forward to hearing from you
Thank you in advance
Good Luck!!

seyha moth
Go to Top of Page

seyha_moth
Yak Posting Veteran

74 Posts
Posted - 2006-12-11 : 20:39:40
Can you help me?

seyha moth
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-12-12 : 05:37:22
Isnt it just you need to place the columns in the correct order in the SELECT statement?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site