Join Comma Separated Same Value

Question

create table dbo.CourseMaster
(
  CourseId char(2),
  CourseName char(3)
);

create table dbo.StudentMaster
(
  ROLLNO char(5),
  NAME varchar(10),
  ADDRESS varchar(20),
  Course varchar(100)
);

insert into dbo.CourseMaster values
('01', 'ABC'),
('02', 'DEF'),
('03', 'GHI'),
('04', 'JKL'),
('05', 'MNO'),
('06', 'PQR'),
('07', 'STU');

insert into dbo.StudentMaster values
('12345', 'RAM',   'RAM ADDRESS',   '01,02,02'),                      
('25695', 'HARI',  'HARI ADDRESS',  '02,06'),                         
('89685', 'JEFF',  'JEFF ADDRESS',  '03,05,06,07'),                   
('47896', 'DAISY', 'DAISY ADDRESS', '03');

Query 1:

select SM.ROLLNO,
       SM.NAME, 
       SM.ADDRESS,
       (
       select ','+CM.CourseName
       from dbo.CourseMaster as CM
       where ','+SM.Course+',' like '%,'+CM.CourseId+',%'
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as Course
from dbo.StudentMaster as SM;

Actual Ouput:

| ROLLNO |  NAME |       ADDRESS |          Course |
|--------|-------|---------------|-----------------|
|  12345 |   RAM |   RAM ADDRESS |         ABC,DEF |
|  25695 |  HARI |  HARI ADDRESS |         DEF,PQR |
|  89685 |  JEFF |  JEFF ADDRESS | GHI,MNO,PQR,STU |
|  47896 | DAISY | DAISY ADDRESS |             GHI |

When I add course like 01,02,02 in student master it give below output: ABC,DEF and it should give: ABC,DEF,DEF

Answer 1

Normalising StudentMaster.Course will help preserve duplicates. Since you've tagged sql-server-2012 sadly string_split() is unavailable. Happily, the internet is rife with pre-2016 workarounds. I'll choose this one arbitrarily since it was the top google hit for me.

Given creation of dbo.fn_split_string_to_column() from the linked SO post, the following should get you what you seek.

with normal_course as (
    select SM.ROLLNO,
           ss.column_id,
           ss.value as course_id
    from dbo.StudentMaster as SM
    cross apply dbo.fn_split_string_to_column(SM.Course,',') as ss
)
select SM.ROLLNO,
       SM.NAME, 
       SM.ADDRESS,
       (
       select ','+CM.CourseName
       from normal_course as nc 
       join dbo.CourseMaster as CM on CM.CourseId = nc.course_id
       where nc.ROLLNO = SM.ROLLNO
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as Course
from dbo.StudentMaster as SM;

Try it on dbfiddle

Of course, my preference would also be to move the subselect down to a dedicated apply, but that's not strictly required.

Explanation

In your original query, the WHERE clause in your sub-select is fuzzy-matching the CourseId to the comma-aggregated string Course. This "mostly works" as you can see; but one of the (many) downsides of this approach is what you have seen - that each CourseId matches only once and in-line "duplicates" will not be aggregated multiple times.

Splitting out StudentMaster.Course into normalised values occurs in the CTE (with block) at the top of the query. This preserves the duplicates.

    select SM.ROLLNO,
           ss.column_id,
           ss.value as course_id
    from dbo.StudentMaster as SM
    cross apply dbo.fn_split_string_to_column(SM.Course,',') as ss

For the example duplicate row, this shows us...

+--------+-----------+-----------+
| ROLLNO | column_id | course_id |
+--------+-----------+-----------+
| 12345  | 1         | 01        |
| 12345  | 2         | 02        |
| 12345  | 3         | 02        |
+--------+-----------+-----------+

Now that the duplicate course_id is exposed as a separate data record, when you re-aggregate the corresponding CourseNames, the duplicates will be preserved unless you take steps to explicitly remove them.

Answer 2

With SQL 2016 or later this would be much easier as you could:

1. Use the STRING_SPLIT() table valued function to pull apart the string
2. Call that via CROSS APPLY as illustrated in the examples in the documentation linked in "1"
3. Join to your lookup table
4. Use STRING_AGG() (or the FOR XML PATH hack you are already using, of course) to put it back together

With older versions of SQL Server you can still use the same method by writing your own version of the STRING_SPLIT table valued function, or finding one online (this is the first reasonable looking reference that came up from a quick search, but beware that I've not tested it at all).

Another option would by a scalar valued function that does the job per value, taking in the numbers string and returning the course codes string by looping through and looking up each number.

None of these options will be particularly efficient.

Of course, it should be noted that storing a comma separated list, or otherwise storing multiple data items in a formatted string, is usually a bad idea unless the data is just a blob of stuff that has no meaning to your SQL (for instance, perhaps a chunk of JSON or an XML fragment that will only ever be interpreted by code elsewhere in your stack, so your DB does nothing but store and retrieve it). If you have control of the schema, consider normalising this part of the structure by introducing a student & course link table so you can more easily and efficiently perform this sort of query.

Answer 3

This can be simply achieved in newer version of SQL Servers, with following builtin functions:

1. STRING_SPLIT (SQL 2016)
2. STRING_AGG (SQL 2017/vnext)

However, in your case, following query would give you desired result:

;with StringSplit as 
    (
        select sm.*, cm.CourseName
        from (
                SELECT  Split.a.value('.', 'NVARCHAR(MAX)') CourceID, 
                        a.NAME,
                        a.ADDRESS
                FROM
                (
                    SELECT CAST('<X>'+REPLACE(sm.Course, ',', '</X><X>')+'</X>' AS XML) AS String,
                            *
                    FROM StudentMaster as sm
                ) AS A
                CROSS APPLY String.nodes('/X') AS Split(a)
            ) as sm 
        left join CourseMaster as cm on sm.CourceID = cm.CourseId
        ) 

--select * from StringSplit

select  NAME, ADDRESS,
        STUFF   (
                    (SELECT ',' + CourseName 
                     FROM StringSplit xs
                     WHERE xs.name = ss.name
                     FOR XML PATH ('') ),1,1,''
                ) as StringAgg
from StringSplit as SS
group by NAME, ADDRESS

With New Version

    select sm.Name, sm.ADDRESS 
           ,STRING_AGG  (cm.CourseName, ',') as StringAgg
    from dbo.StudentMaster as sm
        cross apply string_split (sm.Course, ',') as split
        left join CourseMaster as cm on split.value = cm.CourseId
    Group by Name, ADDRESS