1

I have two tables, Students and Race. Each student in the Students table has one row. Each student in the Race table has multiple rows, one for each race.

I'm trying to combine the tables into a view where I have one row for each student and each of their races are listed in separate columns; for example:

Example

Any help or advice is greatly appreciated!

1
  • 1
    have you tried select distinct and inner join? – Alen Mar 28 '19 at 19:59
1

You will need to use the PIVOT function with a INNER JOIN to accomplish this:

SELECT StudentId
     , FName
     , LName
     , CASE H WHEN 0 THEN NULL ELSE 'H' END AS [Hispanic]
     , CASE W WHEN 0 THEN NULL ELSE 'W' END AS [White]
     , CASE B WHEN 0 THEN NULL ELSE 'B' END AS [Black]
     , CASE A WHEN A THEN NULL ELSE 'A' END AS [Asian]
     , CASE I WHEN 0 THEN NULL ELSE 'I' END AS [Indian]
 FROM (SELECT s.StudentId, FName, LName, r.[Race Code]
         FROM [Students] AS s
         JOIN [Race] AS r ON r.StudentId = s.StudentId)
PIVOT (COUNT([StudentId])
         FOR [Race Code] IN ([H],[W],[B],[A],[I])) AS p

If you are happy with just having the count as the row values then there is no need for the case statements and the query can be simplified to this:

SELECT StudentId
     , FName
     , LName
     , [H] AS [Hispanic]
     , [W] AS [White]
     , [B] AS [Black]
     , [A] AS [Asian]
     , [I] AS [Indian]
 FROM (SELECT s.StudentId, FName, LName, r.[Race Code]
         FROM [Students] AS s
         JOIN [Race] AS r ON r.StudentId = s.StudentId)
PIVOT (COUNT([StudentId])
         FOR [Race Code] IN ([H],[W],[B],[A],[I])) AS p
0

I ended up with a solution that looks like this. I like the idea of doing a PIVOT also, I think it'd be much easier to read and probably perform faster. Haven't gotten a chance to check it out yet, but I really appreciate everyone's help with this problem!

Solution

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.