SQL Pivot an unpivoted table

Question

I have a table as follows:

+----+------------+     
| ID | SchoolName |   
+----+------------+     
|  1 | abc        |     
|  1 | pqr        |    
|  1 | xyz        |     
|  2 | dfg        |     
|  2 | yui        |     
|  3 | gtr        |     
|  4 | fgh        |      
|  5 | erf        |     
|  6 | abc        |     
|  6 | fgy        |     
+----+------------+

I want to make it as

+----+--------------------+    
| ID | First Second Third |    
+----+--------------------+    
|  1 | abc   pqr    xyz   |    
|  2 | dfg   yui          |    
|  3 | gtr                |    
|  4 | fgh                |     
|  5 | erf                |    
|  6 | abc   fgy          |    
+----+--------------------+       

I wrote a query:

WITH cte AS (
  SELECT id, schoolname,      
  ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) rn     
FROM dbo.Temp123         
  )    
  SELECT * FROM (   
 (SELECT id, schoolname,rn FROM cte) a    
 PIVOT    
(MAX(schoolname) FOR rn IN (1,2,3)) p

Error:

Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '1'.

Can you please help me fix this query. Thanks

Answer 1

One method is to use row_number(). I would do this with conditional aggregation:

select id,
       max(case when seqnum = 1 then schoolname end) as schoolname_1,
       max(case when seqnum = 2 then schoolname end) as schoolname_2,
       max(case when seqnum = 3 then schoolname end) as schoolname_3
from (select t.*, row_number() over (partition by id order by (select null)) as seqnum
      from dbo.Temp123  t
     ) t
group by id;

Answer 2

You were close! Try this:

;WITH cte AS (
  SELECT id, schoolname,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) rn
  FROM dbo.Temp123
)
SELECT *
FROM  (SELECT id, schoolname,rn FROM cte) a
  PIVOT (MAX(schoolname) FOR rn IN ([1],[2],[3])) p

• removed extra open parenthesis and wrapped integer column identifiers in square brackets

rextester demo: http://rextester.com/GBBN40959

returns:

+----+-----+------+------+
| id |  1  |  2   |  3   |
+----+-----+------+------+
|  1 | abc | pqr  | xyz  |
|  2 | dfg | yui  | NULL |
|  3 | gtr | NULL | NULL |
|  4 | fgh | NULL | NULL |
|  5 | erf | NULL | NULL |
|  6 | abc | fgy  | NULL |
+----+-----+------+------+