|
janetb
Yak Posting Veteran
71 Posts |
 Posted - 2006-03-22 : 11:21:10
|
| I've got the query below giving me the results I need (results 1) but I need the results to be one row of data by person with the first column (sessionNbr) combined into one column per person (results 2). (I left out some of the columns in the results examples for brevity's sake.) This will be a rarely executed query and only contains a total record count return of less than 5,000 rows. What is the best method of achieving this in your opinion? Thanks to all for any help....query:select left(s.session_name,charIndex(' ',s.session_name)-1) as sessionNbr, rs2.firstname, rs2.lastname, rs2.organization_name, rs2.address, rs2.city_name, rs2.state_initials, rs2.zip, rs2.event_fk, rs2.person_pk from ncahec.dbo.tblSession s, (select 'XXX-XX-' + right(person_id,4) as ssn, y.firstname, y.lastname, isNull(o.organization_name,'') as organization_name, isNull(y.home_address1,'') + ' ' + isNull(y.home_address2,'') as address, isNull(y.home_zip,'') as zip, c.city_name, t.state_initials, rs1.event_fk, y.person_pk from tblPerson y left join ncahec.dbo.tblAffiliation a on a.affiliation_pk=y.primary_affiliation_fk left join tblOrganization o on o.organization_pk=a.organization_fk left join tblCity c on y.home_city_fk=c.city_pk left join tblState t on c.state_fk=t.state_pk, (select r.event_fk, r.person_fk, registration_pk from tblRegistration r where (r.registration_status_fk=1) and (r.event_fk=11266) ) as rs1 where rs1.person_fk=y.person_pk) as rs2 where (s.session_status_fk=1) and (rs2.event_fk=s.event_fk) and (left(s.session_name,1) in ('0','1','2','3','4','5','6','7','8','9')) order by rs2.person_pk, case when charIndex('~',left(s.session_name,10))>0 then 1 when charIndex('-',left(s.session_name,10))>0 then 2 end, left(s.session_name,charIndex(' ',s.session_name)-1)Results 1sessionNbr, firstName, lastName, person_pk, event_fk2 John Smith 11266 4095 John Smith 11266 4096 John Smith 11266 40915 John Smith 11266 4094 Jane Doe 11266 5288 Jane Doe 11266 5289 Jane Doe 11266 52819 Jane Doe 11266 52820 Jane Doe 11266 528Results 2sessionNbrs, firstName, lastName, person_pk event_fk2, 5, 6, 15 John Smith 11266 4094, 8, 9, 19, 20 Jane Doe 11266 528 |
|
Crosstab Results Query?