Join Comma Separated Same Value

1 vote

3 answers

2782 views

sql-server sql-server-2012 string-aggregation

                              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(), 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




                        

Asked by patel akash (11 rep)

Sep 9, 2019, 08:51 AM
Last activity: Sep 9, 2019, 10:25 AM

Join Comma Separated Same Value

Related Questions