I'm trying to make a report for someone's poorly designed database and can't figure out how to handle this situation. This is in MSSQL. I searched for an answer to what I needed and couldn't find this scenario. When I run the following query:
SELECT 
  a.episode, 
  a.SUL, 
  b.element 
FROM TableA as a
  LEFT JOIN TableB as b ON (a.ElementKey = b.ElementKey)
WHERE a.episode = '99999'
It returns results that look like this:
| episode | SUL | element |
|---------|-----|---------|
| 99999   | 0   | SLC     |
| 99999   | 1   | SL 1    |
I need them to look like:
| episode | substation | team |
|---------|------------|------|
| 99999   | SLC        | SL1  |
The database basically has 2 records for each record. 1 that has a SUL = 0 (with a element name) and 1 with a SUL = 1 with a (with a team name). I've tried joins and groupBy's and case statements, but nothing that gets me what I need. Thanks!
Edit: More information.
TABLE A:
| Name       | Type   |
|------------|--------|
| episode    | bigint |
| SUL        | int    |
| elementKey | bigint |
Table B:
| Name       | Type    |
|------------|---------|
| elementKey | bigint  |
| element    | varchar |
I didn't do a good job of explaining. Basically every 'episode' on 'Table A' will have 2 rows. One with a SUL = 0 and one with SUL = 1. If it is equal to 0 then I get the 'Table B' element and assign it as 'Substation'. If it is equal to 1 then I get the 'Table B' element and assign it as 'Team'
