Change table structure

[yuchieh]

I have a table like this --
person1 school1
person1 school2
person1 school3
person2 school1
person2 school2
person2 school3
person2 school4

I would liek to create a view in SQL to have a table like this --
person1 school1 school2 school3
person2 school1 school2 school3 school4

I am not quite sure how to do this view. Can anyone help?

Thank you.

 

[kathryn]

Sorry, but I have to ask, why do you want to do this? By doing this you are creating a non-normalized view and things usually get funky when you do that.

Kathryn

 

[yuchieh]

some users in the office want to have the view like this.
Any idea?

 

[tlbroadbent]

If your table has a counter for each person/school combination such as...

Person School RecID
person1 school1 1
person1 school2 2
person1 school3 3
person2 school1 1
person2 school2 2
person2 school3 3
person2 school4 4

then I have a very nice solution using a single query. Otherwise, I can't think of any way to acomplish this without using a cursor.

Here is my solution.

Select
Person,
max(Case When RecID=1 Then School else null end) as 'School1',
max(Case When RecID=2 Then School else null end) as 'School2',
max(Case When RecID=3 Then School else null end) as 'School3',
max(Case When RecID=4 Then School else null end) as 'School4'

From MyTbl

Group By Person

Please let me know if you have any questions about this query. Terry

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

 

[yuchieh]

Thanks for the solution.
Unfortunately, we don't have the "counter" field as you showed there. I assume I can add that field in the table. Could you give me an sample query for adding this field?

Thanks again.

 

[Cagliostro]

alter table add column(x identity) John Fill

ivfmd@mail.md

 

[Andel]

/*
Here's an example:
If your table name is mySchool
and your columns is Person and School
*/

select
person,
school1 = max(case when recno = 1 then school else '' end),
school2 = max(case when recno = 2 then school else '' end),
school3 = max(case when recno = 3 then school else '' end),
school4 = max(case when recno = 4 then school else '' end)
from
(select *, recno = (select count(school) from myschools s
where s.school <= myschools.school
and s.person = myschools.person)
from myschools) as sch
group by person

Andel
andelbarroga@hotmail.com

http://andyb.8m.com