How to get concatenated name as concatinated id in sql server

Question

I have student lookup table as

Student Table

Student id  Student name
1               Sonu
2               Janu
3               Sameer
4               Shri

Course Table

Course Name  Student Id
JAVA          1,2,3
SQL            2,3
HTML           3,4
PHP            2,1

I am having another table i.e course table, in that table I am just entering the student id's with comma separated values. now I wanted to get the student names using the student id's which is there in the Course Table and also student name should be there in the same order as student id that is stored in the Course Table

Result should be

Course Name  Student Name
JAVA           Sonu,Janu,Sameer
SQL            Janu, Sameer
HTML           Sameer,Shri
PHP            Janu,Sonu

Can any one please help to solve this

MY sql version is :Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Why did you denormalize your data? Regardless, you're going to want to [split your values](http://www.sqlservercentral.com/articles/Tally+Table/72993/), join, and then [group concat](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) if you want these results. This would be one method and those links will get you where you want to be. — S3S, May 23 '18 at 18:02
There is no efficient way to do this. Just fix the data structure -- learn what a junction/association table is. — Gordon Linoff, May 23 '18 at 18:07
I need to use ids because the name for ids may change in future but wherever we want to refer this course table we need to show with student names. I tried to use splitting the id's and again concatenating as student name but order is not be same as id's order. I need same order also. — Ashwini, May 23 '18 at 18:18
If you split the ids and got the results just in the wrong order, then maybe a little experimentation with ORDER BY would fix your effort. If not, please post it. — Tab Alleman, May 23 '18 at 18:44
Of course you want to use the IDs. That isn't what people are questioning. It is the decision to store those values a delimited string that is the issue. This violates 1NF and causes nothing but anguish. If you do the hard work designing a good data model querying the data is very easy. The data you are modeling here requires 3 tables, not 2. — Sean Lange, May 23 '18 at 19:09

score 1 · Answer 1 · answered May 23 '18 at 20:09

What you are looking for is a typical m:n relation.

You have got Students, you have got Courses. Both of them exist independantly. Now you want to store, which student takes which course. Therefore you need a mapping table in between:

CREATE TABLE Student(ID INT NOT NULL CONSTRAINT PK_Student PRIMARY KEY
                    ,FirstName NVARCHAR(200) NOT NULL
                    ,LastName NVARCHAR(200) NOT NULL);

INSERT INTO Student(ID,FirstName,LastName) 
                    VALUES(1,'Sonu','Jones')
                         ,(2,'Janu','Smith')
                         ,(3,'Sameer','Miller')
                         ,(4,'Shri','Wilson');

CREATE TABLE Course(ID INT NOT NULL CONSTRAINT PK_Course PRIMARY KEY
                   ,Title NVARCHAR(200) NOT NULL);

INSERT INTO Course(ID,Title) 
                   VALUES(1,'JAVA')
                        ,(2,'SQL')
                        ,(3,'HTML')
                        ,(4,'PHP');

CREATE TABLE CourseStudent(ID INT IDENTITY CONSTRAINT PK_CourseStudent PRIMARY KEY
                          ,CourseID INT NOT NULL CONSTRAINT FK_CourseID FOREIGN KEY REFERENCES Course(ID)
                          ,StudentID INT NOT NULL CONSTRAINT FK_StudentID FOREIGN KEY REFERENCES Student(ID));

INSERT INTO CourseStudent VALUES(1,1),(1,2),(1,3) --Course 1 is taken by 1,2,3
                               ,(2,2),(2,3)       --Course 2 is taken by 2 and 3
                               ,(3,3),(3,4)
                               ,(4,2),(4,1);

--this is a typical select you'd use to get the data mapped

SELECT c.Title
      ,s.FirstName
FROM Course AS c
INNER JOIN CourseStudent AS cs ON c.ID=cs.CourseID
INNER JOIN Student AS s ON s.ID=cs.StudentID

--and this is the select you'd use to get the attending students as concatenated string:

SELECT c.Title
      ,STUFF(
              (
                SELECT ', ' + s.FirstName 
                FROM CourseStudent AS cs
                INNER JOIN Student AS s ON cs.StudentID=s.ID 
                WHERE cs.CourseID=c.ID
                FOR XML PATH(''),TYPE
              ).value('.','nvarchar(max)'),1,2,''
            ) AS StudentName
FROM Course AS c;

I'm using a rather odd trick with XML to achieve the grouped string concatenation. SQL-Server 2017+ introduces STRING_AGG() for this.

@Ashwini It is very kind of you to say *Thank you*, but it would be even kinder to tick the acceptance check (below the answer's vote counter). This will pay reputation points to both of us. Once you've crossed the 15 points border yourself you are asked - additionally - to vote on contributions. This is the SO-way to say thank you. Happy Coding. — Shnugo, May 29 '18 at 15:32

How to get concatenated name as concatinated id in sql server

1 Answers1