Sql-server – Combining Two Base Tables with a 1:M relationship Into One View with Distinct Rows

sql servert-sql

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!

Best Answer

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
Related Question