How to select a group of select columns in a conditional statement

• TC-416047

  Ten Centuries

  Points: 1312

  More actions

  February 12, 2014 at 10:55 am

  #285576

  I'm not sure how to ask this.

  This is very common.

  Assume you have an invoice and two possible addresses to use:

  1. Send_To Address

  2. Bill_To Address

  If there is no Bill_To address use the Send_To Address.

  I would like to do something like this:

  --Pseudo code---

  case Bill_To

  when Bill_To is null

  select Send_To address block

  else

  select Bill_To Address block

  end as [Bill To Address]

  Is there a way to group a block of select lines in t-SQL?

  Example:

  Reference this block of select columns

  -- Sold To

  ,c.NAME as [Sold To Name]

  ,c.ADDR_1 as [Sold To Add1]

  ,c.ADDR_2 as [Sold To Addr2]

  ,c.ADDR_3 as [Sold To Addr3]

  ,c.CITY as [Sold To City]

  ,c.STATE as [Sold To State]

  ,c.ZIPCODE as [Sold To Zip]

  ,c.COUNTRY as [Sold To Country]

  as Sold_To

  and use it on the case statement.

  Yes this is a case of being lazy.

  Thanks in advance.

• Lowell

  SSC Guru

  Points: 323504

  More actions

  February 12, 2014 at 10:59 am

  #1688511

  the isnull or coalesce functions can help there:

  coalesce lets you grab more than one alternative, and even finish with a default if all columns were null.

  SELECT

  ISNULL(Send_To_Address,Bill_To_Address) AS Bill_To_Address,

  COALESCE (Send_To_Address,Bill_To_Address,Mail_To_Address, 'Missing') AS Bill_To_Address2

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• TC-416047

  Ten Centuries

  Points: 1312

  More actions

  February 12, 2014 at 3:22 pm

  #1688571

  Lowell,

  Thank you.

  Any thoughts on how SoldToAddress could represent all columns in the address?

  tia,

  Todd