How to get multiple records combined to one using row number?

Question

How can I get multiple alert records (up to 10 per student) to appear on one record per student? I'm attempting to use row number to number the records and then place them in the output, but the code I have written does not work due to incorrect syntax errors on the "where" parameter in the cross apply statement. I've tried multiple ways to accomplish what I need, but can't get past the errors. Please help.

select 
sd.[student_number],
a.[health_alert],
a.[comments],
b.[health_alert],
b.[comments]

from student_enrollmentcube as se,
   student_demographicscube as sd

cross apply (select  a.[health_alert], a.[comments], (select row_number() 
 over (partition by a.[student_id] order by a.[student_id]) as r1 from 
   ar_student_health_alerts) a)  where a.[student_id] = sd.[student_id] and 
    r1 = 1)
cross apply (select  b.[health_alert], b.[comments], (select row_number() 
 over (partition by b.[student_id] order by b.[student_id]) as r2 from 
  ar_student_health_alerts) b)  where b.[student_id] = sd.[student_id] and 
   r2 = 2)

where se.student_id = sd.student_id and
     se.enrollment_status= 'active' and
     se.[current_academic_year] = 'y'

Is this SQL-related? If so, tag appropriately. – genpfault Sep 19 '17 at 19:06 — genpfault, Sep 19 '17 at 19:06
please include the exact error message as well – Wes H Sep 20 '17 at 14:32 — Wes H, Sep 20 '17 at 14:32

score 0 · Answer 1 · answered Sep 20 '17 at 14:38

You have multiple issues with your syntax, including incomplete select statements embedded within other incomplete select statements and even a comparision of a table alias with a literal value.

I'd recommend you break this into pieces. Your cross apply statement needs to be able to run on its own, with the exception of the reference to the outer table.

In other words, get this working:

(select  a.[health_alert], a.[comments], (select row_number() 
 over (partition by a.[student_id] order by a.[student_id]) as r1 from 
   ar_student_health_alerts) a)  where a.[student_id] = sd.[student_id] and 
    r1 = 1)

before you trying using it in a cross apply.

How to get multiple records combined to one using row number?

1 Answers1