Hello guys, I've posted on here before- its been a while- but I can't remember what my username was and none of email work.
regardless I'm in need of some expert help. I have a table reg_user that has a row for each response (to a questionnaire) that a parent submits. I'm trying to get all the responses (3 in this case) to be in one row for each student.
this is my script:
select distinct r.STUDENT_ID [StudentID]
,r.FIRST_NAME [FirstName]
,coalesce(r.MIDDLE_NAME,'') [MiddleName]
,r.LAST_NAME [LastName]
,r.GRADE [Grade]
,r.BUILDING [Builing]
,rb.NAME [SchoolName],
case When ru.FIELD_NUMBER = '1' then ru.FIELD_VALUE else null end as [Temporary Living Arrangement],
case when ru.FIELD_NUMBER = '3' then ru.FIELD_VALUE else null end as [Due to Economic Hardship],
case when ru.FIELD_NUMBER = '5' then ru.FIELD_VALUE else null end as [Student Presently Living],
replace(convert(varchar(10),ru.CHANGE_DATE_TIME, 101),'/','') [ChangeDate]
,ru. CHANGE_UID [ChangedBy]
from reg_user ru
join reg r on r.STUDENT_ID = ru.STUDENT_ID
join REG_BUILDING rb on rb.building = r.BUILDING
where r.CURRENT_STATUS = 'A' and ru.STUDENT_ID = XXXXXXX and ru.SCREEN_NUMBER = '20'
right now I get the following results
StudentID,FirstName, [etc.....] Temporary Living Arrangement, Due to Economic Hardship, Student Presently Living, [etc]
12345678,VICTOR, [etc........], Y , null ,null , [etc]
12345678,VICTOR, [etc........], Null , Y ,null , [etc]
12345678,VICTOR, [etc........], Null , Null ,Y , [etc]
I'm not sure what I can do to get the desired results, Any help pointing me to the right direction is greatly appreciated.
Thanks in advance.