Fetch Records from two table in a single row

Bikash Das

14y1.7k

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sukesh Marla14y ago
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.
0
Bikash Das14y ago
Many Many thanks sir.i check it and work fine.
0
Pradip Pandey14y ago
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
0
Jignesh Trivedi14y ago
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.
0
Sukesh Marla14y ago
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.
0