When I don’t want to use aggregate, I should use Pivot to convert SQL Server 2008 row value to column names?

teacher student
------------ ----------
teacher1 Bob
teacher1 Jim
teacher2 Sam
teacher3 Bill
teacher3 John
teacher3 Eric

teacher1 teacher2 teacher3
--------------- ------------------
Bob Sam Bill
Jim null John
null null Eric

DECLARE @teacherList AS VARCHAR( max)

SELECT @teacherList = Stuff((SELECT DISTINCT',[' + teacher +']'
 FROM myTable
 FOR xml path('')), 1, 1,'')

DECLARE @dynamic_pivot_query AS VARCHAR(max)

SET @dynamic_pivot_query ='select' + @teacherList + 
'from 
 (
 SELECT [teacher], [student]
 FROM [dbo].[myTable]
) as S
Pivot
(
 MIN([student])
 FOR teacher IN ( '+ @teacherList +')
) as P
'
EXEC(@dynamic_pivot_query)

SET @dynamic_pivot_query ='select '+ @teacherList + 
'from 
(
 SELECT [teacher],[student], row_number() over(partition by teacher order by student) as rn
 FROM [dbo].[myTable]
) as S
Pivot
(
 MIN([student])
 FOR teacher IN ('+ @teacherList +')
) as P
'

SELECT @teacherList = Stuff((SELECT DISTINCT',' + quotename(teacher)
 FROM myTable
 FOR xml path('' )), 1, 1,'')

I am trying to master the manipulation of table data into a more visually appealing output format. This may be part of the problem, because What I want may be for a separate reporting software.

I have a table that looks like this

teacher student
----------------------
teacher1 Bob
teacher1 Jim
teacher2 Sam
teacher3 Bill
teacher3 John
teacher3 Eric

I want a table that looks like this:< /p>

teacher1 teacher2 teacher3
------------------------------ ---
Bob Sam Bill
Jim null John
null null Eric

So I try to fill in all the teacher names in the variable, and then use Pivot, but since I have to Select aggregation, so I can only get Max or Min students like this:

DECLARE @teacherList AS VARCHAR(max)

SELECT @teacherList = Stuff ((SELECT DISTINCT',[' + teacher +']'
 FROM myTable
 FOR xml path('')), 1, 1,'')

DECLARE @ dynamic_pivot_query AS VARCHAR(max)

SET @dynamic_pivot_query ='select' + @teacherList + 
'from 
(
 SELECT [teacher],[student]
 FROM [dbo].[myTable]
) as S
Pivot
(
 MIN([student])
 FOR teacher IN ('+ @teacherList + ')
) as P
'
EXEC(@dynamic_pivot_query)

The result is:

teacher1 teacher2 teacher3 
---------------------------------
Bob Sam Bill 

Assumptions are as follows:

>Teacher# and his name are unknown (variable)
>The number of students for each teacher is unknown, and each teacher may be different

Is there a way to do this?

You can use row_number to get the desired result.

SET @dynamic_pivot_query = 'select '+ @teacherList + 
'from 
(
 SELECT [teacher],[student], row_number() over(partition by teacher order by student) as rn
 FROM [dbo].[myTable]
) as S
Pivot
(
 MIN([student])
 FOR teacher IN ('+ @teacherList +')< br />) as P
'

Update:
To remove the SQL injection vulnerability, you should use quotes to quote your field list correctly.

SELECT @teacherList = Stuff((SELECT DISTINCT',' + quotename(teacher)
 FROM myTable
 FOR xml path('')), 1, 1,'')