SQL Server T-SQL Collate Multiple Rows for a given column into a single cell following a join

Anonymous 61 Reputation points
2022-10-31T23:44:23.207+00:00

Hi,

I have a table as below:

255847-image.png

Now for a given instructor ID my query need to select something like this:

255826-image.png

Please note I will pass in the InstructorID in the query or output will be based on a join of another table based on InstructorID as foreign key.

Can you help me solve this please? Thanks in advance.

Developer technologies Transact-SQL
0 comments No comments
{count} votes

Accepted answer
  1. Jingyang Li 5,896 Reputation points Volunteer Moderator
    2022-10-31T23:54:37.627+00:00
    SELECT InstructorID , STRING_AGG(CONVERT(NVARCHAR(max), LearnerID), '|')   
    WITHIN GROUP (ORDER BY LearnerID ASC) AS LearnerIDs   
    FROM yourtable  
    GROUP BY InstructorID ;  
    

1 additional answer

Sort by: Most helpful
  1. LiHongMSFT-4306 31,566 Reputation points
    2022-11-01T01:45:43.197+00:00

    Hi @Anonymous
    Try this query:

    SELECT InstructorID ,  
           STUFF((SELECT '|' + CAST(LearnerID AS VARCHAR(50)) FROM TableName WHERE InstructorID =T.InstructorID  FOR XML PATH(''))  
    		    ,1,1,'')AS LearnerIDs   
    FROM (SELECT DISTINCT InstructorID  FROM TableName)T  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.