Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cross Tab Query

Status
Not open for further replies.

Manishreya

Programmer
Dec 27, 2000
14
US
I am using sql 7.0

I want to use cross tab for one of my table
I have data for perticular Student

Example

StId Degree
1 Bs
1 Ms
1 Phd
1 Diploma

From educate table

I want to disply as
StId D1 D2 D3 D4
1 Bs Ms Phd Diploma

How to do this as I can not use Sum as the fielddatatype is Varchar

Can any one help to solve this problem


Thanks
ManiShreya
 
This should work.

Select
StID,
max(Case When Degree='BS' Then Degree else null end) as 'D1',
max(Case When Degree='MS' Then Degree else null end) as 'D2',
max(Case When Degree='PhD' Then Degree else null end) as 'D3',
max(Case When Degree='Diploma' Then Degree else null end) as 'D4',

From Educate

Group By StID
Terry
 
Thanks
Max function is not accepting

is there is any other way to do this
 
Another way is to link the table to itself, as in:

SELECT D1.StID AS StId,
D1.Degree as D_1,
D2.Degree as D_2,
D3.Degree as D_3,
Q4.Degree as D_4
FROM
educate as D1, educate as D2, educate as D3, educate as D4
WHERE
D1.StId=D2.StId and
D1.StId=D3.StId and
D1.StId=D4.StId;

Hope this helps,
Michael
 
Thanks

This Multiple join condition generating multiplerecords
I need to disply only one record
Any other way to do this please
 
If you do self-joins you will end up with the following result. In total 256 (or 4^4) unique rows will be returned. The query needs more criteria to prevent repeating columns and appearance of columns in different order in each row.

StId D_1 D_2 D_3 D_4
----------- ---------- ---------- ---------- ----------
1 Bs Bs Bs Bs
1 Bs Bs Bs Diploma
1 Bs Bs Bs Ms
1 Bs Bs Bs Phd
1 Bs Bs Diploma Bs
1 Bs Bs Diploma Diploma
1 Bs Bs Diploma Ms
1 Bs Bs Diploma Phd
1 Bs Bs Ms Bs
1 Bs Bs Ms Diploma
1 Bs Bs Ms Ms
1 Bs Bs Ms Phd
1 Bs Bs Phd Bs
1 Bs Bs Phd Diploma
1 Bs Bs Phd Ms
1 Bs Bs Phd Phd
1 Bs Diploma Bs Bs
1 Bs Diploma Bs Diploma
1 Bs Diploma Bs Ms
1 Bs Diploma Bs Phd
1 Bs Diploma Diploma Bs
.
. Lot's of rows
.
1 Phd Phd Phd Diploma
1 Phd Phd Phd Ms
1 Phd Phd Phd Phd
Terry
 
Sorry, I had an extra comma. This should work. If not, please tell us the error message.

Select
StID,
max(Case When Degree='BS' Then Degree else null end) as 'D1',
max(Case When Degree='MS' Then Degree else null end) as 'D2',
max(Case When Degree='PhD' Then Degree else null end) as 'D3',
max(Case When Degree='Diploma' Then Degree else null end) as 'D4'

From Educate

Group By StID Terry
 
You're right, i miss read the question. Sorry about that. :(
 
Thanks

This is working for me

This table consists of multiple students.
can I do this Dynamically as degrees for particular student varies I am trying to generate view.

Do you have any tips for me
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top