0

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'

0 Answers0