How to solve this query Bandi [Resolved]

Posted by Jayakumars under ASP.NET AJAX on 3/30/2015 | Points: 10 | Views : 402 | Status : [Member] [MVP] | Replies : 6
Hi
Bandi here i have 2 tables your response depends single table.
can you solve this.can u did and mail me.

My Table1 Output like this

EmpId EmpName
1 Jones
2 John
3 David
4 Romer

My Table2 output like this

EmpId EmpName
1 Jones-Desc
1 John-Desc
2 David-Desc
3 3David-Desc
2 2David-Desc


But I need output like this

EmpId EmpName
1 Jones-Desc,John-Desc
2 David-Desc,2David-Desc
3 3David-Desc

How will do this any one guide me.

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 3/30/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
;WITH Cte AS (
SELECT t2.EmpId, t2.EmpName
FROM Table1 t1
JOIN Table2 t2 ON T1.EmpId = t2.EmpId)
SELECT EmpId, STUFF((SELECT ','+EmpName FROM Cte WHERE EmpId=c1.EmpId FOR XML PATH('') ), 1,1, '') EmpName
FROM Cte c1
GROUP BY EmpId


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 3/30/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

;With CourseAllocation AS
(SELECT c.CourseName, s.StudentName
FROM CourseAllot ca
JOIN Course c ON c.CourseId = ca.CourseId
JOIN Students s ON s.StudentId = ca.StudentId )
SELECT Row_Number() OVER( ORDER BY CourseName) Sno, CourseName, STUFF((SELECT ','+StudentName FROM CourseAllocation WHERE CourseName=c1.CourseName FOR XML PATH('')),1,1,'') StudentNames
FROM CourseAllocation c1
GROUP BY CourseName


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/30/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Bandi

My Table Structure with data like this


Create Table Course
(
CourseId int Primary key Identity(1,1),
CourseName Varchar(50)
)
Insert into Course values('C#')
Insert into Course values('Asp.net')
Insert into Course values('Sqlserver')
Insert into Course values('MySql')

Create Table Students
(
StudentId int Primary key identity(1,1),
StudentName varchar(30)
)
Insert into Students values('John')
Insert into Students values('David')
Insert into Students values('Hendry')
Insert into Students values('Smith')
Insert into Students values('Watson')



Create Table CourseAllot
(
AllotId int Primary key identity(1,1),
CourseId int,
StudentId int
)

Insert into CourseAllot values (1,1)
Insert into CourseAllot values (1,1)
Insert into CourseAllot values (2,1)
Insert into CourseAllot values (1,2)
Insert into CourseAllot values (3,4)
Insert into CourseAllot values (3,5)

--I need Output

Sno Course Name Student Name
1 C# John,Hendry,David
2 Asp.net John
3 Sqlserver Smith,WatSon

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/30/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Bandi
Good One I have lot of queries . can you send test mail for me.I send mail to u. can you solve and send me.

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/30/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
bandi

I need also CourseId,AllotId need

check this query but i got error can u solve this

;With CourseAllocation AS
(SELECT c.CourseName, s.StudentName
FROM CourseAllot ca
JOIN Course c ON c.CourseId = ca.CourseId
JOIN Students s ON s.StudentId = ca.StudentId )
SELECT Row_Number() OVER( ORDER BY CourseName) Sno,CourseId,AllotId,CourseName, STUFF((SELECT ','+StudentName FROM CourseAllocation WHERE CourseName=c1.CourseName FOR XML PATH('')),1,1,'') StudentNames
FROM CourseAllocation c1
GROUP BY CourseName



Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 3/30/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
you can add CourseId to the output, but not AllotID...

;With CourseAllocation AS 
(SELECT ca.AllotId, ca.CourseId, c.CourseName, s.StudentName
FROM CourseAllot ca
JOIN Course c ON c.CourseId = ca.CourseId
JOIN Students s ON s.StudentId = ca.StudentId )
SELECT CourseId, CourseName, STUFF((SELECT ','+StudentName FROM CourseAllocation WHERE CourseName=c1.CourseName FOR XML PATH('')),1,1,'') StudentNames
FROM CourseAllocation c1
GROUP BY CourseId,CourseName


My question for allotId is that , what is your expected output if you want AllotID in the result?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response