How to convert transaction Rows into Columns on the new Table

[dadazs]

Hello

I am working on transations table that logs staff appointments.
There can be three appointments (AP1, AP2, AP3) and termination.
Each appointment has a date.

My objective is to physically convert Staff Rows into columns in the the table e.g.

Staff Appointment1 Date1 Successful1
John AP1 12/01/2011 No
John AP2 12/02/2011 No
John AP3 15/02/2011 No
John Termination 16/02/2011 Yes

I need to convert to table like this

Staff Appointment1 Date1 Successful1 Appointment2 Date2 Successful2 Appointment3 Date3 Successful3 Termination

I have googled and found the beginning of solution on MSDN. In general, it suggests using PIVOT table. Pivot tables creates columns for appointments and termination; however, dates are missing.

Please advise

 

[simian336]

Is all your data that clean or is this a best case senario. By that, I mean could there be 5 visits and you only want 3 etc.

Could you post a little bit larger sample with more then one staff?

Simi

 

[Alan0568]

As Simi has said, need some more detail but shooting from the hip and assuming that the first appointment always exists .....

select 
ap1.staff,
ap1.appointment1,
ap1.date1,
ap1.successful1,
ap2.appointment2,
ap2.date2,
ap2.successful2,
ap3.appointment3,
ap3.date3,
ap3.successful3,
term.termination,
term.termination_date,
term.termination_successful

from 
(select 
staff,
appointment1,
date1,
successful1
from appointments
where appointment1 = 'ap1') ap1

left join (select 
			staff,
			appointment1 as appointment2 ,
			date1 as date2,
			successful1 as successful2
			from appointments
			where appointment1 = 'ap2') ap2 ON
			ap1.staff=ap2.staff
			
	left join (select 
			staff,
			appointment1 as appointment3 ,
			date1 as date3,
			successful1 as successful3
			from appointments
			where appointment1 = 'ap3') ap3 ON
			ap1.staff=ap3.staff
			
		left join (select 
			staff,
			appointment1 as termination ,
			date1 as termination_date,
			successful1 as termination_successful
			from appointments
			where appointment1 = 'termination') term ON
			ap1.staff=term.staff

 

[dadazs]

Please see a sample below.
Data is not very clean It is the best case scenario.
it is possible to have "be 5 visits and you only want 3 etc."
However, currently it is not the biggest issue. Major concern is convert to "wide" table so i could easily link with with other tables around the applications.


USERID ONDATE ACTVCODE RESULTCODE pin
AJM 2011-05-23 AP1 COM 16680
AJM 2011-05-23 AP1 COM 666
AJM 2011-05-23 AP1 NS 486745
AJM 2011-05-23 AP1 COM 12345
AJM 2011-05-23 AP1 COM 11111
AJM 2011-05-23 AP1 NS 22222
AJM 2011-05-23 AP1 NS 66666
AJM 2011-05-25 AP2 NS 12345
AJM 2011-05-30 AP2 COM 9387
AJM 2011-05-31 AP1 NS 11111
AJM 2011-06-06 AP3 NS 21564
AJM 2011-06-17 HRT COM 16640
AJM 2011-06-29 AP3 COM 16701
AJM 2011-07-12 AP1 NS 2145
RPD 2011-08-10 AP3 NS 16863
RPD 2011-08-10 AP3 NS 16725
RPD 2011-08-10 AP3 NS 16834
RPD 2011-08-30 AP3 NS 16882
RPD 2011-08-30 AP3 NS 16848
RPD 2011-08-30 AP3 NS 16821
RPD 2011-08-30 AP3 NS 16853
RPD 2011-09-22 AP2 EL 17173

 

[Alan0568]

So you only want ap1,ap2 & ap3 ?

and can you confirm if an ap1 record would always exist, or could you get data for a staff member with just ap2 & ap3? (that you would want)

 

[dadazs]

I want AP1, AP2, AP3 & HRT. I could get data for either of values.
All data is based on user's input; therfore, I cannot confirm that AP1 would always exist.

 

[Alan0568]

As we can see from the sample data that appointments are not Unique can you provide a revised required output which caters for this?

 

[dadazs]

Please see a required sample below. I have managed to find a script online that satisfies my requirements:

select pin,
MAX(CASE WHEN ACTVCODE ='AP1' THEN convert(varchar,(convert(date,ONDATE,12))) END) DateAppointment1

Is it a good solution and is there a better way to do it.



pin DateAppointment1 Appointment1 DateAppointment2 Appointment2 DateAppointment3 Appointment3 Termination TerminationDate
666 23/05/2011 COM NULL NULL NULL NULL NULL NULL
2145 12/07/2011 NS NULL NULL NULL NULL NULL NULL
9387 13/06/2011 COM 30/05/2011 COM NULL NULL NULL NULL
11111 31/05/2011 NS NULL NULL NULL NULL NULL NULL
12345 20/07/2011 COM 25/05/2011 NS NULL NULL NULL NULL
16384 25/07/2011 NS NULL NULL NULL NULL NULL NULL
16411 27/06/2011 COM NULL NULL NULL NULL NULL NULL
16422 15/06/2011 NS NULL NULL NULL NULL NULL NULL
16424 13/06/2011 COM NULL NULL NULL NULL NULL NULL
16434 NULL NULL 04/07/2011 COM NULL NULL NULL NULL
16443 22/06/2011 COM NULL NULL NULL NULL NULL NULL
16525 NULL NULL 22/06/2011 COM NULL NULL NULL NULL
16579 29/06/2011 COM NULL NULL NULL NULL NULL NULL
16630 13/06/2011 NS NULL NULL 27/06/2011 COM NULL NULL
16632 15/06/2011 COM NULL NULL NULL NULL NULL NULL
16640 13/06/2011 NS NULL NULL NULL NULL 17/06/2011 COM

 

[Alan0568]

Sorry but in your new sample you now don't have staff (userid) and are now using RESULTCODE as appointment.

If you have found a script that does what you want then go with that.