I have a table
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
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
[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.
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())', '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'.',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…
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
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');
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;
| 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 |
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);
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(), 2)', 'varchar(max)') as SCHEME from TB_DETAIL as D;