SQL Union parameter question

• jjprachyl

  SSC-Addicted

  Points: 409

  More actions

  April 12, 2010 at 3:46 pm

  #220383

  Hi all,

  This is my first post here, I hope that there is someone that can assist. I am very thankful that this community exist and continues to be free for all of us to gather and to post here.

  I am trying to match what my current reporting tool is doing. It does not share its SQL code, and is some missing functionality(concatenating columns). I want to avoid manually editing the results. This report will get used as a daily feeding another application.

  Q1

  select person, type, number from table1 where type=a, b, c, d

  union

  Q2

  select person, type, number from table1 where type=e order by number

  What I want to do is omit or hide the types from Q1's results and only show Q2's types in the final results without manually removing them.

  Is this possible? How is it done?

  Thank you in advance!:-)

  -JJ

• seth delconte

  SSCertifiable

  Points: 6388

  More actions

  April 12, 2010 at 4:07 pm

  #1150411

  What I want to do is omit or hide the types from Q1's results and only show Q2's types in the final results

  I'm not sure what you want exactly...do you want to substitute some blank text for the actual 'type' field's values in Q1? If so, you can do this:

  Q1

  select person, type='', number from table1 where type=a, b, c, d

  union

  Q2

  select person, type, number from table1 where type=e order by number

  That would effectively return the record values from Q1 and Q2 sans the 'type' values from Q1.

  _________________________________
  seth delconte
  http://sqlkeys.com

• Ben Teraberry

  SSChampion

  Points: 11845

  More actions

  April 12, 2010 at 4:08 pm

  #1150412

  You mean like this?

  select [person],

  case [type]

  when 'e' then 'e'

  else null end as type,

  [number]

  from table1 where [type] in ('a', 'b', 'c', 'd', 'e')

  order by [person],[type]

  └> bt
  

  ---

  
  Forum Etiquette: How to post data/code on a forum to get the best help[/url]