Display in Columns, not Rows

Transact-SQL
1

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.

2

THe proper way is a pivot table, but you can also use your method with a group by which most people did prior to sql2005.

select  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],
sum(case When ru.FIELD_NUMBER = '1' then ru.FIELD_VALUE else null end) as [Temporary Living Arrangement],
sum(case when ru.FIELD_NUMBER = '3' then ru.FIELD_VALUE else null end) as [Due to Economic Hardship],
sum(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] 
,max(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'
group by  r.STUDENT_ID [StudentID]
,r.FIRST_NAME [FirstName] 
,coalesce(r.MIDDLE_NAME,'')
,r.LAST_NAME 
,r.GRADE  
,r.BUILDING
,rb.NAME
[
3

Thanks for pointing me in the right direction. I'm not very familiar with pivot tables, but i will do some research and try it. Thanks again.