-1

enter image description here

Please see my SQL questions from the picture above.

how to write the SQL for this?

Li Yuan
  • 17
  • 6
    Don't post screenshots of text. If you have a question, type it here. – BSMP Jun 25 '17 at 17:14
  • 2
    That seems to be pretty basic PIVOT. If this is a homework question, you should probably look to your course documents for the expectations. Also, https://stackoverflow.com/questions/7674786/mysql-pivot-table. – Shawn Jun 25 '17 at 17:45

1 Answers1

3
DECLARE @Columns nvarchar(MAX),@SQL nvarchar(MAX);
SET @Columns=N'';
SELECT @Columns+=IIF(@Columns='', 
QUOTENAME(continent),N','+QUOTENAME(continent))
from(Select continent from student group by continent ) as x ;
Set @SQL=N'SELECT ['+STUFF(@Columns,1,1,'')+'
FROM ( Select  continent,name,row_number() over(partition by continent order 
by name) rn from student ) AS j
PIVOT( MAX( name) FOR continent IN ( ['+STUFF(@Columns,1,1,'')+')) as p';
EXECUTE  sp_executesql @SQL;
Hitesh Thakor
  • 471
  • 2
  • 12