Is it possible to join a SELECT from one table with another SELECT from other table in a single row?

Question

I have a table called "Studies" and it has these columns:

+-------------+---------------+-------------+------------+-------------+
| CodeStudent |   Type Study  | Title Study | Date Study | Place Study |
+-------------+---------------+-------------+------------+-------------+
|  10         |   Technical   |   TitleOne  | 01-02-2005 |    Narnia   |
+-------------+---------------+-------------+------------+-------------+
|  10         | Technological |   TitleTwo  | 01-05-2009 |    Mars     |
+-------------+---------------+-------------+------------+-------------+
|  10         |   University  |  TitleThree | 01-08-2012 | Gotham City |
+-------------+---------------+-------------+------------+-------------+
|  20         |   Technical   |  OtherTitle | 01-06-2011 |    Namek    |
+-------------+---------------+-------------+------------+-------------+

And I have a table called "Students" with the following columns and information:

+-------------+---------------+-------------+------------+
| CodeStudent |      Name     |   LastName  |  BirthDate |  
+-------------+---------------+-------------+------------+
|  10         |      Hug      |   Lobezno   | 02-02-2002 |
+-------------+---------------+-------------+------------+
|  20         |      Son      |    Gokú     | 05-06-2007 |
+-------------+---------------+-------------+------------+

Where the following should be considered:

The CodeStudent column of the "Students" table is a primary key and the CodeStudent column of the "Studies" table is its respective foreign key
The only types of studies that exist are Technical, Technological and University
It is necessary to say that not all students have the three types of studies (Technical, Technological and University), some of them may have two studies, a single study (as in the case of Son Goku student) or none.

Do I need to know if it is possible to show all the information in a single row?

If the student's information with code 10 is requested, it should show this:

+-------------+---------------+-------------+------------+-----------------+---------------+----------------+--------------------+-------------------+--------------------+-----------------+----------------+-----------------+
| CodeStudent |      Name     |   LastName  |  BirthDate |  TitleTechnical | DateTechnical | PlaceTechnical | TitleTechnological | DateTechnological | PlaceTechnological | TitleUniversity | DateUniversity | PlaceUniversity |
+-------------+---------------+-------------+------------+-----------------+---------------+----------------+--------------------+-------------------+--------------------+-----------------+----------------+-----------------+
|  10         |      Hug      |   Lobezno   | 02-02-2002 |    TitleOne     |   01-02-2005  |     Narnia     |      TitleTwo      |    01-05-2009     |        Mars        |    TitleThree   |   01-08-2012   |    Gotham City  |
+-------------+---------------+-------------+------------+-----------------+---------------+----------------+--------------------+-------------------+--------------------+-----------------+----------------+-----------------+

And if the student's information with code 20 is requested, it should show this:

+-------------+---------------+-------------+------------+-----------------+---------------+----------------+--------------------+-------------------+--------------------+-----------------+----------------+-----------------+
| CodeStudent |      Name     |   LastName  |  BirthDate |  TitleTechnical | DateTechnical | PlaceTechnical | TitleTechnological | DateTechnological | PlaceTechnological | TitleUniversity | DateUniversity | PlaceUniversity |
+-------------+---------------+-------------+------------+-----------------+---------------+----------------+--------------------+-------------------+--------------------+-----------------+----------------+-----------------+
|  20         |      Son      |   Goku      | 05-06-2007 |    OtherTitle   |   01-06-2011  |     Namek      |       NULL         |       NULL        |        NULL        |       NULL      |      NULL      |       NULL      |
+-------------+---------------+-------------+------------+-----------------+---------------+----------------+--------------------+-------------------+--------------------+-----------------+----------------+-----------------+

I know there is a way and it is this:

SELECT
      S.CodeStudent, 
      S.Name,
      S.LastName,
      S.BirthDate, 
      (SELECT TOP 1 ST.TitleStudy FROM Studies ST WHERE ST.CodeStudent = 10 AND ST.TypeStudy = 'Technical') AS TitleTechnical,
      (SELECT TOP 1 ST.DateStudy FROM Studies ST WHERE ST.CodeStudent = 10 AND ST.TypeStudy = 'Technical') AS DateTechnical,
      ...
FROM Student S
WHERE S.CodeStudent = 10

But is there a better and optimal way to do it? :p

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Andreas, Nov 06 '17 at 22:15
NEver use correlated subqueries, they are performance killers. — HLGEM, Nov 06 '17 at 22:40

SqlZim · Accepted Answer · 2017-11-06T22:23:43.190

using conditional aggregation to pivot the set of columns for each type of study:

select
    S.CodeStudent
  , S.Name
  , S.LastName
  , S.BirthDate
  , TitleTechnical     = max(case when t.TypeStudy = 'Technical' then t.TitleStudy end)
  , DateTechnical      = max(case when t.TypeStudy = 'Technical' then t.DateStudy end)
  , PlaceTechnical     = max(case when t.TypeStudy = 'Technical' then t.PlaceStudy end)
  , TitleTechnological = max(case when t.TypeStudy = 'Technological' then t.TitleStudy end)
  , DateTechnological  = max(case when t.TypeStudy = 'Technological' then t.DateStudy end)
  , PlaceTechnological = max(case when t.TypeStudy = 'Technological' then t.PlaceStudy end)
  , TitleUniversity    = max(case when t.TypeStudy = 'University' then t.TitleStudy end)
  , DateUniversity     = max(case when t.TypeStudy = 'University' then t.DateStudy end)
  , PlaceUniversity    = max(case when t.TypeStudy = 'University' then t.PlaceStudy end)
from Student S
  left join Studies T  -- left join to support querying students with no studies
    on s.CodeStudent = t.CodeStudent
where S.CodeStudent = 10
group by 
    S.CodeStudent
  , S.Name
  , S.LastName
  , S.BirthDate

rextester demo: http://rextester.com/SJONU26439

Is it possible to join a SELECT from one table with another SELECT from other table in a single row?

1 Answers1