CODEWITHSUNDEEP
sql-server
sapter
sapter

Reputation: 344

Get rows from different table in a column in select query

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.

Upvotes: 0

Views: 59

Answers (1)

JNevill
JNevill

Reputation: 50273

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

Upvotes: 2

Related Questions