Fetch Records from two table in a single row

Question

suppose i have two table like

Student
------------------------

Roll	Name	Address
1	Bikash	bbsr
2	Tapan	ctc
3	Saroj	puri
4	Manoj	bbsr

Book Issue
-------------------------------------

Roll	Book	Date
1	C#	21/03/2012
2	C programming	21/03/2012
1	Sql Server	03/04/2012
2	C++	03/04/2012

Result should be
---------------------------------

Roll        Name                Book                   Date
--------------------------------------------------------------------------------------------------------
1           Bikash               C#,Sql Server                        21/03/2012,03/04/2012
2           Tapan                c programming,c++               21/03/2012,03/04/2012

please give me solution. already i have used group by with role number but not working.

Pradip Pandey · Answer

Hi Bikash, I just go through your query and found that u have to create 2 functions and a select query to get the desired result. ALTER FUNCTION [dbo].[GetAliasesById] ( @Roll int ) RETURNS varchar(max) AS BEGIN declare @output varchar(max) select @output = COALESCE(@output + ', ', '') + book from BookIssue --AS A Inner Join Student AS B ON A.Roll = B.Roll where Roll = @Roll return @output END GO Alter FUNCTION [dbo].[GetAliasesById1] ( @Roll int ) RETURNS varchar(max) AS BEGIN declare @output varchar(max) select @output = COALESCE(@output + ', ', '') + convert(varchar,cast(date as varchar),110) from BookIssue where Roll = @Roll return @output END GO SELECT distinct A.Roll, A.Name,dbo.GetAliasesByID(A.Roll) As Book , dbo.GetAliasesById1(A.roll) As Date FROM Student As A Inner Join BookIssue As B ON A.Roll = B.Roll GROUP BY A.Roll,A.Name GO

Jignesh Trivedi · Answer

hi, try.... just some modification on above query... SELECT * FROM ( SELECT r.Roll, r.name, STUFF((SELECT ','+ a.Book FROM BookIssue a WHERE a.Roll = r.Roll FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') as books, STUFF((SELECT ','+ convert(varchar(10),a.Date,103) FROM BookIssue a WHERE a.Roll = r.Roll FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') as Dates FROM Student r) A Where A.books is not null hope this will help you.

Sukesh Marla · Answer

Select * from ( SELECT Student.Roll,Student.Name, replace ( ( SELECT BookIssue.Book as [data()] FROM BookIssue WHERE BookIssue.Roll=Student.Roll ORDER BY BookIssue.Roll FOR xml path('') ) , ' ', ',' ) as Books, replace ( ( SELECT BookIssue.Date as [data()] FROM BookIssue WHERE BookIssue.Roll=Student.Roll ORDER BY BookIssue.Roll FOR xml path('') ), ' ', ',' ) as Date FROM Student ) DerivedStudents where Books is not null Hope It Helped. Please Check This is Currect Answer .

Sukesh Marla · Answer

If it really helped then check this is correct answer for right answer.So that others also come to knw, when they search for such queries.

Bikash Das · Answer

Many Many thanks sir.i check it and work fine.

Fetch Records from two table in a single row

Answers (5)