sql server programming: Here's a simplified version of the scenario (and, before anyone asks, NO I
can't change the structure - it's inherited and it is what it is):

Table1
StudentID, int
Lastname
Firstname

Table2
StudentID
ParentLastName
ParentFirstName

Is there a set-based solution to taking those two tables and putting one row
per studentid into a table that has a structure that REQUIRES the first TWO
parents listed in table 2 - putting in blanks if only one parent exists:

Table3
StudentID
Lastname
Firstname
Parent1LN
Parent1FN
Parent2LN
Parent2FN

If not, what is the correct syntax for populating that third table?
Populate all the values from Table1 into Table3 first, then cursor on
Table3's StudentID and query on Table2 to get the values of the Parents??

Just not sure how to automatically process this information.

Jeff

you could do something like this...

create table Table1(
StudentID int,
Lastname sysname,
Firstname sysname)
create table Table2(
StudentID int,
ParentLastName sysname,
ParentFirstName sysname)
go
insert table1 select 1,'abc','abc'
union all select 2,'def','def'
insert table2 select 1,'l1','f1'
union all select 1,'l2','f2'
union all select 2,'l3','f3'
go
select studentid,lastname,firstname,max(case when cnt=1 then parentlastname
end) p1ln,
max(case when cnt=1 then parentfirstname end) p1fn,
max(case when cnt=2 then parentlastname end) p2ln,
max(case when cnt=2 then parentlastname end) p2fn
from (
select t1.*,t2.parentlastname,t2.parentfirstname,(select count(*)
from table2
where studentid=t1.studentid and parentfirstname<=t2.parentfirstname)cnt
from table1 t1 left join table2 t2 on t1.studentid=t2.studentid)x
group by studentid,lastname,firstname
go
drop table table1,table2
go

you could also use RAC for these simple crosstabs plus others. check site
for more info.

--
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net


[quoted text, click to view]

Jeff,

There's always brute force (below). I used oj's
tables more or less, and since you didn't indicate
what you mean by the "first" two parents in table 2,
I wrote it as if you meant first in order by Last, First
names. You can change the relevant order by clauses to
apply a different meaning of "first" two.

create table Table1 (
StudentID int not null,
Lastname varchar(20),
Firstname varchar(20),
primary key (StudentID)
)
create table Table2 (
StudentID int not null,
ParentLastName varchar(20),
ParentFirstName varchar(20),
primary key (StudentID, ParentLastName, ParentFirstName),
)
go
insert table1 select 1,'abc','abc'
union all select 2,'def','def'

insert table2 select 1,'l1','f1'
union all select 1,'l2','f2'
union all select 2,'l3','f3'
go


select
studentid,
lastname,
firstname,
Parent1LN,
Parent1FN,
case when Parent1LN + Parent1FN = Parent2LN + Parent2FN
then '' else Parent2LN end Parent2LN,
case when Parent1LN + Parent1FN = Parent2LN + Parent2FN
then '' else Parent2FN end Parent2FN
from (
select
studentid,
lastname,
firstname,
(select top 1 ParentLastName
from Table2
where StudentID = table1.StudentID
order by ParentLastName, ParentFirstName
) Parent1LN,
(select top 1 ParentFirstName
from Table2
where StudentID = table1.StudentID
order by ParentLastName, ParentFirstName
) Parent1FN,
(select top 1 ParentLastName
from (
select top 2 ParentFirstName, ParentLastName
from Table2
where StudentID = table1.StudentID
order by ParentLastName, ParentFirstName
) P order by ParentLastName desc, ParentFirstName desc
) Parent2LN,
(select top 1 ParentFirstName
from (
select top 2 ParentFirstName, ParentLastName
from Table2
where StudentID = table1.StudentID
order by ParentLastName, ParentFirstName
) P order by ParentLastName desc, ParentFirstName desc
) Parent2FN
from table1
) T
go
drop table table1,table2

-- Steve Kass
-- Drew University
-- Ref: F6067297-2A03-4764-B1BB-DEEF67F13268

[quoted text, click to view]