Get values for the same ID into 1 row separated by a comma in SQL Server 2008 R2

Question

0.00/5 (No votes)

See more:

I have a query that generates this

StudentID |Building |Period |Grade
12345           2            1       11
12345           2            5       11
43210           2            1       12

But I need a way to group the Periods by the StudentID:

StudentID |Building |Period | Grade
12345           2            1,5     11
43210           2            1        12

This is my query:

select r.student_id [StudentID],
r.building [Building],
ab.attedancePeriod [Period],
r.grade [Grade]
from register r
inner join att_bottom ab on ab.student_id = r.student_id
where ab.building = '2'
and ab.attendance_c = 'T'

Any suggestions are more than welcomed!

Update
The query below retrieves all of the StudentID's assigned to building 2 regardless if they have an attendance code of T or not for the specific date (today's date).
It's assigning a NULL value to the ones that don't when I just need the ones that do (which will be the ones with a period).

SQL

select r.student_id [StudentID],
           r.building [Building],
           (select ab.attendancePeriod + ','
                from att_bottom ab
                where ab.student_id = r.student_id
                and ab.building = '2'
                and ab.attendance_c ='T'
                AND CONVERT(varchar,ab.attendance_date,102) = convert(varchar,getdate(),102)
               FOR XML PATH ('') ) AS PERIOD,
                r.GRADE

               FROM register r

Result

StudentID |Building |Period |Grade
12345           2            1 ,        11
43210           2            1 ,         12
98775           2           NULL   10

Posted 21-Jan-14 10:18am

Member 10539656

Updated 30-Jan-18 19:59pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2014-01-21T10:27:00

SQL Wizardry Part Seven - PIVOT and arbitrary lists of data[^] shows how to do this, using FOR XML.\

SQL

select r.student_id [StudentID],
r.building [Building],
ab.attedancePeriod [Period],
STUFF
    (
        (
            SELECT ',' + convert(varchar(2), grade)
            FROM register
            WHERE register.student_id = r.student_id
            group BY month(student_id)
            order by month(grade)
            FOR XML PATH('')
        ), 1, 1, ''
    ) AS Grades
 from register r
inner join att_bottom ab on ab.student_id = r.student_id
where ab.building = '2'
and ab.attendance_c = 'T'
group by r.student_id

is my guess, but without the source tables, it's hard to know.

Oh, you may want to try this:

SQL

select r.student_id [StudentID],
           r.building [Building],
           (select ab.attendancePeriod + ','
                from att_bottom ab
                where ab.student_id = r.student_id
                and ab.building = '2'
                and ab.attendance_c ='T'
                AND CONVERT(varchar,ab.attendance_date,102) = convert(varchar,getdate(),102)
               FOR XML PATH ('') ) AS PERIOD,
                r.GRADE

               FROM register r
               inner join att_bottom a on a.student_id = r.student_id
               where a.attendance = 'T'

I think the issue is, you're filtering on the inner level, but not the outer level. The filter on the inner level may be able to be removed.

Shail Mishra · Answer 2 · 2018-01-30T19:59:00

Solution 2

Did you check this ?

Posted 30-Jan-18 19:59pm

Shail Mishra

Comments

Jochen Arndt 31-Jan-18 2:56am

How could he checked in 2014 an SO thread from 2016 without having a time machine?

Please avoid answering old questions. It will usually get downvoted as you might have recognised (it wasn't me).