14,732,982 members
See more:
I HAVE 2 TABLE STUDENT & SUBJECT , AND IN STUDENT TABLE I HAVE 3 COLUMN.

STUDENTID, STUDENTNAME, SUBJECTID

IN MY TABLE VALUE WILL STORED LIKE :

STUDENTID STUDENTNAME SUBJECTID
1 ABC 2,3,4
2 XYZ 1,4,5
3 ABD 4,5,6

AND SUBJECT TABLE:

SUBJECTID SUBJECTNAME
1 HINDI
2 ENGLISH
3 MATH
4 PHYSICS
5 CHEMISTRY
6 BIOLOGY

I WANT A STOREDPROCEDURE WHICH RETURN THE VALUE LIKE

STUDENTID SUBJECT
1 ENGLISH
1 MATH
1 PHYSICS
2 HINDI
2 PHYSICS
2 CHEMISTRY
3 PHYSICS
3 CHEMISTRY
3 BIOLOGY

4
Posted
Nilesh Patil Kolhapur 11-Apr-12 6:26am

set foreign key in subject table on studentId then ur problem ll solve easly
ur Idea is too bad boss

## Solution 1

```CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null  return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end```

above function will return you the subject

execute the function as below:

`select Split('2,3,4',',')`

## Solution 2

Your Table Structure is wrong its not normalized you can use this structure
Table:Studentinfo
StudentID StudentName

1 Binson
2 Manu
3 Anu

Table:SubjectInfo

SubjectID SubjectName
1 Engilsh
2 Malayalam
3 Hindi

Table:StudentSubjectJunction
Note:Both Column must be Primary key
StudentID SubjectID
1 1
1 2
1 3
2 1
2 2
2 3

Now Use this Query:
```CREATE PROC GetStudents
AS
BEGIN

SELECT StudentInfo.name,SubjecInfo.SubjectName

FROM SubStud JOIN StudentInfo ON StudentInfo.ID=SubStud.StudID
JOIN SubjecInfo ON SubjecInfo.ID=SubStud.SubID

END```

If your tables are perfectly normalized in at least 3r level .you can use minimal complex queries