0

This question already has an answer here:

I want to select in one row the value of a column that appears in multiple rows, I have the table Solution:

| StudentID | SolutionDate | SolutionTime | SongID |
----------------------------------------------------
|  0824616  | 2015-09-20   | 00:07:00     |   01   |
|  0824616  | 2015-09-20   | 00:05:00     |   02   |
|  0824616  | 2015-09-21   | 00:07:40     |   01   |
|  0824616  | 2015-09-21   | 00:10:00     |   03   |
|  0824616  | 2015-09-23   | 00:04:30     |   03   |
|  0824616  | 2015-09-23   | 00:11:30     |   03   |

I want to group the records by StudentID and SongID. The expected output is:

| StudentID | SongID |  TimeA   |  TimeB   |  TimeC   |
-------------------------------------------------------
| 0824616   |   01   | 00:07:00 | 00:07:40 |  NULL    |
| 0824616   |   02   | 00:05:00 |  NULL    |  NULL    |
| 0824616   |   03   | 00:10:00 | 00:04:30 | 00:11:30 |

There are 3 records by StudentID-SongID at the most. I'm using MSSQL Server 2012.

marked as duplicate by Andriy M, Michael Green, RolandoMySQLDBA, Max Vernon, Mark Sinkinson Sep 24 '15 at 15:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

First you have to use CTE to find ROW_NUMBER() which will sort record then use LEFT OUTER JOIN to achive your result

-- declare temp table
DECLARE @t1 TABLE (
  StudentID bigint,
  SolutionDate date,
  SolutionTime time,
  SongID int
)

-- insert the data into table
insert into @t1 values (0824616,'2015-09-20','00:07:00',01)
insert into @t1 values (0824616,'2015-09-20','00:05:00',02)
insert into @t1 values (0824616,'2015-09-21','00:07:40',01)
insert into @t1 values (0824616,'2015-09-21','00:10:00',03)
insert into @t1 values (0824616,'2015-09-23','00:04:30',03)
insert into @t1 values (0824616,'2015-09-23','00:11:30',03)

-- use CTE to find ROW_NUMBER()
 ;WITH CTE AS
(   SELECT
*,ROW_NUMBER() OVER(PARTITION BY SongID ORDER BY SongID,SolutionTime) AS ColumnNumber
FROM @t1
)

-- then JOIN between CTE and Temp table
SELECT diSTINCT
t.StudentID,t.SongID
,t1.SolutionTime AS TimeA,t2.SolutionTime AS TimeB,t3.SolutionTime AS TimeC
FROM @t1 t
LEFT OUTER JOIN CTE t1 ON t.SongID=t1.SongID AND t1.ColumnNumber=1
LEFT OUTER JOIN CTE t2 ON t.SongID=t2.SongID AND t2.ColumnNumber=2
LEFT OUTER JOIN CTE t3 ON t.SongID=t3.SongID AND t3.ColumnNumber=3 
ORDER BY t.SongID ASC

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