Here's how to make your query using Jeff Moden's splitter function (of which a slightly modified version is shown below):

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[RowNum] INT IDENTITY(1,1),

[StudentID] INT NOT NULL,

[Subj] NVARCHAR(50) NULL,

PRIMARY KEY (RowNum))

INSERT INTO #TempTable SELECT 101,'EJB;C++;PERL'

INSERT INTO #TempTable SELECT 102,'Dotnet'

INSERT INTO #TempTable SELECT 103,'Java'

INSERT INTO #TempTable SELECT 104,'Oracle;Java;Sql'

INSERT INTO #TempTable SELECT 105,'Sql;SSIS'

DECLARE

@strInput VARCHAR(8000)

,@Delimiter1 CHAR(1)

,@Delimiter2 CHAR(1)

,@Delimiter3 CHAR(1)

SET @Delimiter1 = '|'

SET @Delimiter2 = ','

SET @Delimiter3 = ';'

SELECT

@strInput = REPLACE(COALESCE(@strInput+@Delimiter1,'')

+CAST(CAST(t.StudentID AS VARCHAR(5))

+@Delimiter2+t.Subj AS VARCHAR(MAX)),@Delimiter3,@Delimiter2)

FROM

#TempTable AS t

SELECT

StudentID = (MAX(CASE c.itemnumber WHEN 1 THEN c.item END))

,Subj1 = (MAX(CASE c.itemnumber WHEN 2 THEN c.item END))

,Subj2 = (MAX(CASE c.itemnumber WHEN 3 THEN c.item END))

,Subj3 = (MAX(CASE c.itemnumber WHEN 4 THEN c.item END))

FROM

(

SELECT

@strInput

) a (parameter)

CROSS APPLY

dbo.tvfDelimitedSplit(parameter,@Delimiter1) b

CROSS APPLY

dbo.tvfDelimitedSplit(item,@Delimiter2) c

GROUP BY

b.ItemNumber

The code above requires Jeff Moden's splitter function (and this version requires a separate Tally table as well).

LINK: The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] will show you how to create your own Tally table--very easy.

CREATE FUNCTION dbo.tvfDelimitedSplit

(

@pString VARCHAR(8000)

,@pDelimiter CHAR(1)

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

WITH

cteTally(N)

AS

(

SELECT TOP (ISNULL(DATALENGTH(@pString),0))

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM

dbo.Tally

)

,cteStart(N1)

AS

(

SELECT

1

UNION ALL

SELECT

t.N + 1

FROM

cteTally t

WHERE

SUBSTRING(@pString,t.N,1) = @pDelimiter

)

,cteLen(N1,L1)

AS

(

SELECT

s.N1

,ISNULL(

NULLIF(

CHARINDEX(@pDelimiter,@pString,s.N1),0)

- s.N1,8000)

FROM

cteStart s

)

SELECT

ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)

,Item = SUBSTRING(@pString,l.N1,l.L1)

FROM

cteLen l;

Output: