Cross Tab Query

[Manishreya]

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

 

[tlbroadbent]

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

http://members.home.net/tlbroadbent/index.htm

 

[Manishreya]

Thanks
Max function is not accepting

is there is any other way to do this

 

[mGis]

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

 

[Manishreya]

Thanks

This Multiple join condition generating multiplerecords
I need to disply only one record
Any other way to do this please

 

[tlbroadbent]

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

http://members.home.net/tlbroadbent/index.htm

 

[tlbroadbent]

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

http://members.home.net/tlbroadbent/index.htm

 

[mGis]

You're right, i miss read the question. Sorry about that.

 

[Manishreya]

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