SELECT InstructorID , STRING_AGG(CONVERT(NVARCHAR(max), LearnerID), '|')
WITHIN GROUP (ORDER BY LearnerID ASC) AS LearnerIDs
FROM yourtable
GROUP BY InstructorID ;
SQL Server T-SQL Collate Multiple Rows for a given column into a single cell following a join
Hi,
I have a table as below:
Now for a given instructor ID my query need to select something like this:
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
-
Jingyang Li 5,896 Reputation points Volunteer Moderator
2022-10-31T23:54:37.627+00:00
1 additional answer
Sort by: Most helpful
-
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.