SQL Server - Join Query

Here are my tables

  StudentID    Name
    ----------   ------
      1          Mary
      2          John
      3          Peter
      4          Edwards

   ClassID      StudentIDs
    --------    -----------
      1          1,2
      2          3,4

    **Output Expected**

    ClassID   Names
    -------   -------

        1      Mary,John
        2      Peter,Edwards

Answers


The solution is not hard, even the information in your table does not seems to be stored correctly:

SELECT  ClassID
       ,(SELECT SUBSTRING((SELECT ',' + Name FROM @TableOne WHERE Students LIKE '%'+CAST(StudentID AS NVARCHAR(5))+'%' ORDER BY Name FOR XML PATH('')),2,200) AS CSV)
FROM @TableTwo 

And you can use this code (copy/paste) for working example:

DECLARE @TableOne TABLE
(
    StudentID INT,
    Name NVARCHAR(100)
)

DECLARE @TableTwo TABLE
(
    ClassID INT,
    Students NVARCHAR(100)
)

INSERT INTO @TableOne (StudentID,Name)
VALUES  (1,'Mary')
       ,(2,'John')
       ,(3,'Peter')
       ,(4,'Edwards')
       ,(5,'Pepe')
       ,(6,'C.Ronaldo')
       ,(7,'Zidane')
       ,(8,'Raul')

INSERT INTO @TableTwo (ClassID,Students)
VALUES  (1,'1,3,5,6,8')
       ,(2,'2,4,7')


SELECT  ClassID
       ,(SELECT SUBSTRING((SELECT ',' + Name FROM @TableOne WHERE Students LIKE '%'+CAST(StudentID AS NVARCHAR(5))+'%' ORDER BY Name FOR XML PATH('')),2,200) AS CSV)
FROM @TableTwo 

Need Your Help

Recognise Malformed XML file

python xml malformed

I am building a python script to run nightly, part of which involves invoking a drupal bulk operation to export an XML file. Since the process takes a number of hours, and the next step is to

Addition and Subtraction of Dates in Java

java datetime date

How can we add or subtract date in java? For instance java.sql.Date and formatted like this: yyyy-MM-dd, how can i Add 5 months from that? I've seen in some tutorial that they are using Calendar, c...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.