-2

I'd like to write an SQL statement in code which works like a case statement of some sorts in order to identify that a certain "indicator" represents two different "meanings". When Indicator in the table below = "Underlying" I want an additional row to be added which displaces an additional style

For example (Table Name below is Called "Styles") :

Name| style | agency | type | indicator

Charles | B | MDY | Long | Underlying

Charles | C | FTH | Long | |

Charles | AA- | SP | Long | |

I'd like to run a select query against this table which will return results that creates an additional MDY row whenever indicator = 'Underlying' and essentially split the indicator and type into two different rows. So whenever a row exists where indicator = Underlying and agency = MDY, I'd like to return an output of having two MDY rows (one row with type = long and indicator = NULL and another row with type = NULL and indicator = underlying)

So output would look like this:

Name| style | agency | type | indicator

Charles | B | MDY | Long |

Charles | B | MDY | | Underlying

Charles | C | FTH | Long | |

Charles | AA- | SP | Long | |

John Conde
  • 217,595
  • 99
  • 455
  • 496
alkali009
  • 1

1 Answers1

0

Here's one option using union all:

select name, style, agency, type, indicator
from styles
where indicator != 'underlying' and type != 'long'
union all
select name, style, agency, type, null
from styles
where indicator = 'underlying' and type = 'long'
select name, style, agency, null, indicator
from styles
where indicator = 'underlying' and type = 'long'
sgeddes
  • 62,311
  • 6
  • 61
  • 83