0

I want to be able to see the rows from different table in a single column .

Just like this question How to select row/s from an other table using a comma-separated value from the first table? (which is a duplicate of this question: MySQL Join two tables with comma separated values :) but for Sql Server.

Tables from link:

Table 1. faculty

subject
101, 102
104
103, 105

Table 2. subject

code subject
101 subject 1
102 subject 2
103 subject 3
104 subject 4
105 subject 5

Expected Output:

subject subject
101, 102 subject 1, subject 2
104 subject 4
103, 105 subject 3, subject 5

Basically I don't want to see multiple columns for joins but all corresponding values in on column comma separated.

JNevill
  • 46,980
  • 4
  • 38
  • 63
sapter
  • 184
  • 1
  • 10
  • 2
    Please show us your database schema, some sample data, and the query you have worked on so far. – SchmitzIT Aug 16 '22 at 14:39
  • 1
    What version of sql server are you using? Please also provide sample data and desired results. Trying to write sql for a generic concept like splitting rows to columns and string aggregating post-join isn't easy. – JNevill Aug 16 '22 at 14:39
  • @JNevill my problem is the same as above link. I have replaced Group_Concat with string_concat but Find_in_set doesn't exist in SqlServer , so I can't follow the answer.. – sapter Aug 16 '22 at 14:45
  • Just FYI. I edited the question to link to the linked-duplicate and pulled through relevant information for the next person to stumble on this question and all the important bits will be here. – JNevill Aug 16 '22 at 15:03

1 Answers1

2

To split a delimited string into rows in newer versions of sql server you can use STRING_SPLIT() in a CROSS APPLY. Then after joining, to aggregate strings back into delimited form you can use function STRING_AGG. Putting the same pieces together as the linked question you will get the following. NOTE that this requires newer versions of sql server that support both STRING_AGG and STRING_SPLIT functionality.

With faculty_split AS 
(
  SELECT *
  FROM faculty
    CROSS APPLY STRING_SPLIT (subject, ',') fac
)
SELECT faculty_split.subject as codes, STRING_AGG(subject.subject, ',') as subjects 
FROM faculty_split 
   INNER JOIN subject on faculty_split.value = subject.code
GROUP BY faculty_split.subject

Link to fiddle

JNevill
  • 46,980
  • 4
  • 38
  • 63