Since the OP seems to have disappeared ill post the code.

;WITH data AS (

SELECT 101 AS StudentID, 'EJB;C++;PERL' AS Subject UNION ALL

SELECT 102,'Dotnet' UNION ALL

SELECT 103,'Java' UNION ALL

SELECT 104,'Oracle;Java;Sql' UNION ALL

SELECT 105,'Sql;SSIS')

SELECT StudentID, [1],[2],[3]

FROM

(

SELECT StudentID, ItemNumber, Item

FROM data

CROSS APPLY udf_DelimitedSplit8K (Subject,';')

)p

PIVOT

(

MAX(Item)

FOR ItemNumber IN ([1],[2],[3])

)pvt

we dont need the extra delimiters since the pivot table takes care of adding our nulls. also the original version of jeffs splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/ is probably the most optimized version of it. by using a CTE to build the tally table in memory we ensure that (unless its paged to disk) we have our numbers table in memory and dont have any reads from disk (again baring the engine using tempdb which we have no control over other than adding more memory to our server).

and for the cross tabs we can do the following and still only need the one delimiter

;WITH data AS (

SELECT 101 AS StudentID, 'EJB;C++;PERL' AS Subject UNION ALL

SELECT 102,'Dotnet' UNION ALL

SELECT 103,'Java' UNION ALL

SELECT 104,'Oracle;Java;Sql' UNION ALL

SELECT 105,'Sql;SSIS')

SELECT StudentID, MAX(CASE WHEN ItemNumber = 1 THEN Item ELSE NULL END),

MAX(CASE WHEN ItemNumber = 2 THEN Item ELSE NULL END),

MAX(CASE WHEN ItemNumber = 3 THEN Item ELSE NULL END)

FROM(

SELECT StudentID, ItemNumber, Item

FROM data

CROSS APPLY udf_DelimitedSplit8K (Subject,';'))x

GROUP BY StudentID