Is creating this Pivot table tough? 1

[uticket]

Hi, I have a table in following format

School_ID Student_ID Visit_Date Visit_Number
10 2 July 01, 2003 1
10 2 July 10, 2003 2
11 5 May 5, 2003 1
11 5 June 15 2003 2
.......

As there are only two possible visit number: 1 or 2, I would like to turn above table into following format:

School_ID Student_ID Visit_1_Date Visit_2_Date
10 2 July 01, 2003 July 10, 2003
11 5 May 5, 2003 June 15, 2003

The new table will help me calculate the average dates between visit 1 and visit 2 for every school.

It seems it is much more difficult than I had expected.
Is it really so tough?

Thanks!

 

[uticket]

Hi, anyone please let me know if this can be done or not?

Thanks!

 

[ABerkowicz]

This is a quick way, not the best way to do this using temp table, but it will get you what you need fairly quickly.

select a.school_id, a.student_id, visit_1_date = case
when a.visit_number = 1 then a.visit_date end,
visit_2_date = case
when b.visit_number = 2 then b.visit_date end
into #tempA
from table1 a, table1 b
where a.school_id = b.school_id and a.student_id = b.student_id

select * from #tempA where not visit_1_date is null and not visit_2_date is null

 

[ABerkowicz]

Here is a cleaner way without temp table -


select school_id, student_id, visit_1_date, visit_2_date
from (select a.school_id, a.student_id,
visit_1_date = case
when a.visit_number = 1 then a.visit_date end,
visit_2_date = case
when b.visit_number = 2 then b.visit_date end
from table1 a join table1 b on a.school_id = b.school_id and a.student_id = b.student_id) z
where not visit_1_date is null and not visit_2_date is null