How to add multiple columns dynamically in stroed procedure?

As an alternative to Solution 1 you might like to consider what would happen if a Student_id had more than 3 Nw_id rows. For example with this test data...

Create table test(
  Student_id int,
  Student_name varchar(5),
  Nw_id int,
  Nw_name varchar(5)
  )
Insert into test values(1,'add',23,'cvb')
Insert into test values(2,'wfg',45,'eret')
Insert into test values(2,'wfg',67,'fdg')
Insert into test values(2,'wfg',89,'kkk')
Insert into test values(3,'aha',23,'cvb')
Insert into test values(3,'aha',45,'eret')
Insert into test values(3,'aha',67,'fdg')
Insert into test values(3,'aha',89,'kkl')

If I run the query from Solution 1 (or any alternative static pivot) then I get an extra row for my Student_id = 3 but I don't see the data for Nw_name = 'kkl'!
That's because I need to know how many columns I'm going to need before I do the PIVOT - but that might vary depending on the actual data on the table.

The alternative is to build up a dynamic pivot using dynamic sql within your stored procedure. I'm also going to do this as a single PIVOT because that is my personal preference (for no good reason) and because it also demonstrates another technique that can be used.

The two key points here are
a) I want to know a "row number" for each Nw_name per Student_id - i.e. For Student_id = 2 it would be 1 = eret, 2 = fdg, 3 = kkk. I'm going to use that information later. I'll call it RowN

b) I'm going to combine the Nw_id and Nw_name columns in a fixed style so that I only need to pivot once. I've chosen to format the Nw_id into 4 characters padded with zeroes, followed by the Nw_name. Thus the Nw_name will always start in the 5th position in the results. I'll call it combopivot

This sql will put all of that into a temporary table. I'm using a temporary table because I want to use the information several times later on

SELECT Student_id, Student_name, CAST(REPLICATE('0',4-LEN(Nw_id)) AS varchar(4)) + CAST(Nw_id AS varchar(4)) + Nw_Name as combopivot
,'R' + CAST(ROW_NUMBER() OVER
            (PARTITION BY Student_id ORDER BY Student_name ASC) AS Varchar(3)) AS RowN
into #temp
FROM test;

which, if I query it, will return

Student_id	Student_name	combopivot	RowN
1		add 		0023cvb 	R1 
2		wfg 		0045eret 	R1 
2		wfg 		0067fdg 	R2 
2		wfg 		0089kkk 	R3 
3		aha 		0023cvb 	R1 
3		aha 		0045eret 	R2 
3 		aha 		0089kkl 	R3 
3 		aha 		0067fdg 	R4 

Now I can deal with the unknown number of columns for that PIVOT...I'm using that temporary table and picking up the RowN column

DECLARE @cols varchar(MAX)
SELECT @cols = STUFF((SELECT ',' + [RowN]
              FROM (SELECT DISTINCT RowN FROM #temp) T
              ORDER BY [RowN]
              FOR XML PATH('')), 1, 1, '')

Using STUFF is a nice easy way of creating a comma-separated list in T-SQL. For my test data it produces a varchar containing

R1,R2,R3,R4

Now I need to take care of combopivot... I combined the data so I could just have the one pivot but the format isn't what the requirements stated, so I need to split that column into it's id + name original columns in my result set. Back to that temporary table...

DECLARE @selects varchar(MAX)
SELECT @selects = STUFF( (SELECT ',' + 'SUBSTRING(' + [RowN] + ',1,4) AS ID' + [RowN] + ',SUBSTRING(' + [RowN] + ',5,len(' + [RowN] +')) AS NAME' + [RowN]
                          FROM (SELECT DISTINCT RowN FROM #temp) T
                          ORDER BY [RowN]
                          FOR XML PATH('')),1,1,'')

which will give a sql fragment of

SUBSTRING(R1,1,4) AS IDR1,SUBSTRING(R1,5,len(R1)) AS NAMER1,
SUBSTRING(R2,1,4) AS IDR2,SUBSTRING(R2,5,len(R2)) AS NAMER2,
SUBSTRING(R3,1,4) AS IDR3,SUBSTRING(R3,5,len(R3)) AS NAMER3,
SUBSTRING(R4,1,4) AS IDR4,SUBSTRING(R4,5,len(R4)) AS NAMER4

I inserted some linefeeds just to make it clearer - they are not required in the final query.

Now we can put it all together into a single query string and execute it

DECLARE @sql VARCHAR(MAX)

SELECT @sql = 'SELECT Student_id, Student_name, ' + @selects +
' FROM (SELECT * FROM #temp) as s
PIVOT
(
    MAX(combopivot)
    FOR [RowN] IN (' + @cols + ') ) AS P
ORDER BY 1'

EXEC sp_executesql @sql

which will return

S_id	S_name	IDR1	NameR1	IDR2	NameR2	IDR3	NameR3	IDR4	NameR4
1  	add 	0023 	cvb 	(null) 	(null) (null) 	(null) 	(null) (null) 
2  	wfg  	0045  	eret  	0067  	fdg  	0089  	kkk  	(null) (null) 
3  	aha  	0023  	cvb  	0045  	eret  	0089  	kkl  	0067  	fdg 

I'll admit it looks a bit awkward at first, but it will cater for additional data appearing in the way I described.
All of this has targeted SQL Server 2008 so there may be further improvements to be had with later versions.
For completeness here is the main body of the stored procedure in a single hit

SELECT Student_id, Student_name, CAST(REPLICATE('0',4-LEN(Nw_id)) AS varchar(4)) + CAST(Nw_id AS varchar(4)) + Nw_Name as combopivot
,'R' + CAST(ROW_NUMBER() OVER
            (PARTITION BY Student_id ORDER BY Student_name ASC) AS Varchar(3)) AS RowN
into #temp
FROM test;

DECLARE @cols varchar(MAX)
SELECT @cols = STUFF((SELECT ',' + [RowN]
              FROM (SELECT DISTINCT RowN FROM #temp) T
              ORDER BY [RowN]
              FOR XML PATH('')), 1, 1, '')

DECLARE @selects varchar(MAX)
SELECT @selects = STUFF( (SELECT ',' + 'SUBSTRING(' + [RowN] + ',1,4) AS ID' + [RowN] + ',SUBSTRING(' + [RowN] + ',5,len(' + [RowN] +')) AS NAME' + [RowN]
                          FROM (SELECT DISTINCT RowN FROM #temp) T
                          ORDER BY [RowN]
                          FOR XML PATH('')),1,1,'')

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = 'SELECT Student_id, Student_name, ' + @selects +
' FROM (SELECT * FROM #temp) as s
PIVOT
(
    MAX(combopivot)
    FOR [RowN] IN (' + @cols + ') ) AS P
ORDER BY 1'

EXEC sp_executesql @sql