Return all results from a Query on a single row-CodePudding

I have a query that will return all the results for matching row values between two tables.

The tables are Students and StudentRace. The match in the WHERE statement is STUDENTS.ID = STUDENTRACE.STUDENTID.

I am trying to return the column STUDENTRACE.RACECD where all the matching RACECDs are in a single row in the resulting table. The query does something different, however. IF a STUDENTID has more than 1 RACECD it does not repeat each RACECD in a single row for the STUDENTID. It will return a separate row for each RACECD that matches the STUDENTID.Here is my query:

select 
    STUDENTS.ID as ID,
    STUDENTS.STUDENT_NUMBER as STUDENT_NUMBER,
    STUDENTRACE.STUDENTID as STUDENTID,
    STUDENTS.FIRST_NAME as FIRST_NAME,
    STUDENTS.LAST_NAME as LAST_NAME,
    STUDENTRACE.RACECD as RACECD,
    STUDENTS.ENROLL_STATUS as ENROLL_STATUS
from 
    STUDENTRACE STUDENTRACE,
    STUDENTS STUDENTS
where 
    STUDENTS.ID = STUDENTRACE.STUDENTID
    and ENROLL_STATUS = 0

Here is the result for a STUDENT ID = 23:

StudentID Racecd
23        B
23        W

This is close but not exactly what I would like to see. I would like the result to be:

StudentID Racecd
23        B,W

or something similar to that. I think I may need the CONCAT function and possibly a nested SELECT statement as well, but I am not sure. I am new to SQL so I am not sure how to move forward.

CodePudding user response：

Like jarlh said I am not sure how you select 7 columns but result in 2? but Listagg is what I think you are looking for. You can separate it by any delimiter (in this case I put comma). Also any outlying columns will need to appear in the group by

 select 
        STUDENTRACE.STUDENTID as STUDENTID,
        listagg(STUDENTRACE.RACECD,',') as RACECD
        from STUDENTRACE STUDENTRACE,
        STUDENTS STUDENTS
        where STUDENTS.ID=STUDENTRACE.STUDENTID
        AND ENROLL_STATUS = 0
    group by STUDENTRACE.STUDENTID

CodePudding user response：

If you want there two records to become one, you want to group by something, aggregating on something else. In this case you want a single record for each student so you can group by the student_id, and you want all races aggregated so you can use GROUP_CONCAT (in MYSQL, but you can use corresponding aggregation function if in other RDBMS) to concatenate the races. It would be like this:

SELECT s.id, s.name, GROUP_CONCAT(sr.race)
FROM students s join studentrace sr on s.id = sr.student_id
GROUP BY s.id

That is the base to get what you want, then you can add the other fields you are interested in on the select and on the where filters.

SQL Fiddle: http://www.sqlfiddle.com/#!9/ad59d6/1/0

Hope that helps.

CodePudding user response：

If you are using Microsoft SQL Server, you might want to try the following code.

create table Person (
  Name nvarchar(450)
)

insert into Person values ('Fabio'), ('Laura')

select stuff((select ','   Name from Person for xml path('')), 1, 1, '')

The above select statement returns the following string.

Fabio,Laura