7

I have a table [CourseMaster] LIKE

CourseId    CourseName
-----------------------
  01          ABC
  02          DEF
  03          GHI
  04          JKL
  05          MNO
  06          PQR
  07          STU

And I have another table [StudentMaster] for Student Details LIKE

ROLLNO  NAME    ADDRESS          Course
------------------------------------------------
12345   RAM     RAM ADDRESS      01,02,06                      
25695   HARI    HARI ADDRESS     02,06                         
89685   JEFF    JEFF ADDRESS     03,05,06,07                   
47896   DAISY   DAISY ADDRESS    03         

Here I want to fetch the Student details with CourseName(Not CourseId).

If the values in Course is not comma separated than it would be very simple query to fetch the details with join.

As of my knowledge I can run two queries for the same result what I want, One query for fetching the details of student from [StudentMaster] to the front end. And other one for only fetching the CourseName from [CourseMaster] by corresponding CourseId through a loop.

But the fact I want the result by only one query rather than write two queries for this small task.

I guess it is 100% possible. And my expected result will look like:

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

Thank you and any valuable suggestion will be highly appreciate.

6
  • 2
    The best would be to normalize your tables and not store comma separated values: Is using multiple foreign keys separated by commas wrong, and if so, why? Aug 27, 2015 at 12:54
  • yes i wonder if i can, but i am working on a existing project so unable to modify the structure sorry Aug 27, 2015 at 12:56
  • 2
    After the normalisation you can always replace [StudentMaster] with a view and use triggers to be used by the current application(s) and then bit by bit change the code until you no longer need the current situation. Not beautiful but you end up with a better database.
    – Marco
    Aug 27, 2015 at 13:23
  • That's very tough task for me Aug 27, 2015 at 13:51
  • 2
    Your predecessor saved some time with an idea that seemed good at the time, but now you are dealing the consequences. It is never too late to fix a problem, you just have to convince your bosses that is really is worth the effort.
    – datagod
    Aug 27, 2015 at 14:11

4 Answers 4

10

You really should have a junction table for the courses a student is taking, rather than jamming comma-separated values into a single tuple. If you think this is the last problem you'll have because of this sub-optimal design, you're in for a big surprise. You really should have the owners of this project go read up on normalization - yes it's painful to change your schema, but so is constantly dealing with the limitations of leaving it like it is.

Anyway, with that said, you need a split function. Since your comma-separated values are numeric, you can get away with a variation on my XML function; there are several others to choose from in this blog post.

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1) = ','
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'varchar(8000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

Now, your query is:

;WITH x AS 
(
  SELECT s.ROLLNO, s.Name, s.Address, c.CourseId, c.CourseName
  FROM dbo.StudentMaster AS s
  CROSS APPLY dbo.SplitStrings_XML(s.Course, default) AS f
  INNER JOIN dbo.CourseMaster AS c
  ON f.item = c.CourseId
)
SELECT ROLLNO, Name, Address, STUFF((
  SELECT ',' + CourseName FROM x AS x2 
  WHERE x2.ROLLNO = x.ROLLNO
  ORDER BY CourseId FOR XML PATH, 
  TYPE).value(N'.[1]',N'varchar(max)'), 1, 1, '')
FROM x
GROUP BY ROLLNO, Name, Address;

Again, this is a complicated solution, and because of your inferior database structure, the next query you have to perform will be equally convoluted and cumbersome. There's a reason this type of design is argued against in just about every blog, essay, or book about the topic...

In more modern versions (SQL Server 2017 and above), you can combine STRING_AGG() and STRING_SPLIT() to make this a little simpler:

SELECT s.ROLLNO, s.NAME, s.ADDRESS, STRING_AGG(c.CourseName, ',')
  FROM dbo.StudentMaster AS s
  CROSS APPLY STRING_SPLIT(s.Course, ',') AS f
  INNER JOIN dbo.CourseMaster AS c
  ON f.value = c.CourseId
  GROUP BY s.ROLLNO, s.NAME, s.ADDRESS;
2
  • Thank you very much for your great suggestion. And now i am looking forward to optimize my database. Aug 27, 2015 at 14:01
  • And I am testing your solution and will get back to you, Thank you again Aug 27, 2015 at 14:02
5

Same solution as the one provided by Aaron Bertrand when it comes to building the comma separated values but a bit different in connecting CourseMaster.CourseId with the values in StudentMaster.Course.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

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,06'),                      
('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;

Results:

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

A simple way to get the list values from the StudentMaster table (and you can join against the results) would be to (with the help of a split string as mentioned earlier replies, and assuming that function returns a column called item for each list item):

SELECT ROLLNO, NAME, ADDRESS, item
FROM StudentMaster
CROSS APPLY fSplitString(Course);
0
select D.DATE,D.DESCP,
   (
   select ','+S.NAME
   from tb_schems as S
   where ',' + D.SCHME + ',' like '%,' + cast(S.id as nvarchar(20)) + ',%'
   for xml path(''), type
   ).value('substring(text()[1], 2)', 'varchar(max)') as SCHEME
from TB_DETAIL as D;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.