CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
DanO
DanO

Reputation: 417

would like to retrieve 1 record from 2 different rows in a table

I have a table that has the columns and Rows (notice no D record for team id 2)

enter image description here

What I need is a query that will allow me to retrieve 1 record, for each team id, with column names based on the Type (O or D in this case) such as:

enter image description here

I have found a query which works, however it does not seem too efficient. Also, I may in the future NEED to break down and have another record where a new standings type is added, such as "G" for Group.

SELECT  s.StandingsId,
    s.StandingsTypeId,
    s.TeamId,
    s.Wins,
    s.Losses,
    s.Ties,
    s.SortOrder,
    s.WinLossPct,
    (select
       ds.Wins
        FROM Standings AS ds
            WHERE ds.StandingsTypeId = 'D'
              AND ds.TeamId = s.TeamId) AS DivisionWins,
   (select
      ds.Losses
        FROM Standings AS ds
            WHERE ds.StandingsTypeId = 'D'
              AND ds.TeamId = s.TeamId) AS DivisionLosses,
   (select
      ds.Ties
        FROM Standings AS ds
            WHERE ds.StandingsTypeId = 'D'
              AND ds.TeamId = s.TeamId) AS DivisionTies,

   (select
      ds.WinLossPct
        FROM Standings AS ds
            WHERE ds.StandingsTypeId = 'D'
              AND ds.TeamId = s.TeamId) AS dWinLossPct,

    t.TeamName,
    t.ConferenceId,
    t.DivisionId,
    t.GroupId
  FROM Standings s

  LEFT JOIN Team t ON
            s.TeamId = t.TeamId 
   WHERE s.StandingsTypeId = 'O'

Thanks in Advance.

Upvotes: 1

Views: 393

Answers (2)

roman
roman

Reputation: 117370

Looks like you just need to turn some rows into columns, like this.

select
    s.TeamId,
    max(case when s.Type = 'O' then s.Wins end) as OWins,
    max(case when s.Type = 'O' then s.Losses end) as OLosses,
    max(case when s.Type = 'O' then s.WinPCT end) as OWinPCT,
    max(case when s.Type = 'D' then s.Wins end) as DWins,
    max(case when s.Type = 'D' then s.Losses end) as DLosses,
    max(case when s.Type = 'D' then s.WinPCT end) as DWinPCT
from Standings as s
group by s.TeamId

sql fiddle demo

It's also looks like there's a typo in your second recordset - Team 1 has Dlosses = 2 in first record set and Dlosses = 1 in second.

Upvotes: 1

Conrad Frix
Conrad Frix

Reputation: 52645

A simple SELF JOIN is all you need

SELECT o.id, 
       o.teamid, 
       o.wins   Owins, 
       o.losses olosses, 
       o.winpct owinpct, 
       d.wins   dwins, 
       d.losses dlossses, 
       d.winpct dwinpct 
FROM   standings o 
       LEFT JOIN standings d 
              ON o.teamid = d.teamid 
                AND d.type = 'D' 
WHERE o.type = 'O'

DEMO

If you needed a new type G you add another left join and the new fields to the select

   LEFT JOIN standings g 
          ON o.teamid = g.teamid 
            AND g.type = 'G'

DEMO

Upvotes: 2

Related Questions