CODEWITHSUNDEEP
sqlsql-serverrows
Fhd.ashraf
Fhd.ashraf

Reputation: 537

Displaying values one above the other instead of in the same line which belong to the same row

I have a row in which two different teams are displace with their ID and name.I want to display them in a dropdown.For that I need to display them in a Format which is one above the other.THis is my query and the Image is the current result I am getting..

SELECT Match_Schedule.Match_Serno as 'Id', FirstHomeTeam.Serno as 'HomeTeamID', FirstHomeTeam.Team_Name as 'HomeTeam',SecondHomeTeam.Serno as 'AwayTeamID',SecondHomeTeam.Team_Name as 'AwayTeam'
 FROM Match_Schedule 
 INNER JOIN  Team_Detail AS FirstHomeTeam ON Match_Schedule.HomeTeam = FirstHomeTeam.Serno
 INNER JOIN Team_Detail AS SecondHomeTeam ON Match_Schedule.AwayTeam = SecondHomeTeam.Serno
 where Match_Serno=436

The result I get is enter image description here

But the result I want is.enter image description here

Thank you.

Upvotes: 2

Views: 217

Answers (3)

Timothy Walters
Timothy Walters

Reputation: 16874

No need for fancy UNPIVOT or sub-select statements, the following will give you what you need:

SELECT MS.Match_Serno AS 'Id', T.Serno AS 'TeamID', T.Team_Name AS 'TeamName'
FROM Match_Schedule MS
JOIN Team_Detail T
    ON MS.HomeTeam = T.Serno
    OR MS.AwayTeam = T.Serno
WHERE MS.Match_Serno = 436

You can leave off the first column if you want, I just included it for reference.

Upvotes: 0

Khan
Khan

Reputation: 18162

You acheive this by doing an UNPIVOT on your Match_Schedule table and then doing a JOIN to the Team_Detail table to get the team name.

SELECT U.TeamID, TD.Team_Name
FROM Match_Schedule
UNPIVOT (TeamID FOR TeamName IN (HomeTeam, AwayTeam)) U
JOIN Team_Detail TD ON U.TeamID = TD.Serno
WHERE Match_Serno = 436

Upvotes: 1

John Bingham
John Bingham

Reputation: 2006

You need to massage your data into producing two rows, then query those rows with an outer query like this:

SELECT Id, TeamID
FROM (
    SELECT Serno as ID, Team_Name as TeamID, 'H' as TeamType 
    FROM Team_Detail 
    WHERE Serno = (SELECT HomeTeam FROM Match_Schedule WHERE Match_Serno = 436)
    UNION ALL
    SELECT Serno as ID, Team_Name as TeamID, 'A' as TeamType 
    FROM Team_Detail 
    WHERE Serno = (SELECT AwayTeam FROM Match_Schedule WHERE Match_Serno = 436)
)
ORDER BY TeamType DESC

Upvotes: 0

Related Questions