Combining columns

Question

Hello

I've got the query below with the columns StudentID, StartDate, ExamDate and ResultDate. I want to return the 2nd row set StudentID and xDate.

I could do it using union queries, but is there a cleaner way. I've also tried pivoting, but it doesn't give me the required results.

Thanks in advance Roy

Answer 1

Hi @RoyB09 ,

Please try the following:

Create Table #std (StudentID INT,StartDate Datetime, ExamDate Datetime, ResultDate Datetime);    
GO    
INSERT INTO #std VALUES (101,'01/01/2019','01/06/2019','01/07/2019');    
INSERT INTO #std VALUES (102,'01/01/2020','01/06/2020','01/07/2020');    
INSERT INTO #std VALUES (103,'01/01/2021','01/06/2021','01/07/2021');   
  
SELECT StudentID, DatesFrom, FORMAT(Dates, 'yyyy/MM/dd') AS Dates  
FROM  
(SELECT StudentID, StartDate, ExamDate, ResultDate FROM #std) as p	  
UNPIVOT( Dates FOR DatesFrom IN (StartDate, ExamDate, ResultDate)) AS unstd  

Thanks!

----------

If the above response is helpful, please "accept as answer" and up-vote it. Thanks!

Answer 2

What do you mean by you want to return the second resultset? Other than the student ID what do they have in common? The second resultset includes 3 values for each student whereas the first only has a single value. Do you want to combine these together such that your second result is returned but merged with the data for the first? In that case a JOIN is what you want.

SELECT a.StudentId, a.StartDate, a.ExamDate, a.ResultDate, b.xDate
FROM table1 a LEFT JOIN table2 b ON a.StudentId = b.StudentId

I'm using a left join here so all the rows in table1 will be included and any matching rows in table2 will be merged in.