SQL - SELECT STATEMENT, if one field is true, get from collection of columns

Hi,


Here is what I want to accomplish:

I want to get shipping address , if exists, otherwise use the fields from regular address

Table:

ID                      
Addr
CITY
STATE
SHIPAddr
ShipCity
ShipSTATE



I need something like this:

SELECT ID,
 (IF SHIPPADDR ISNOT NULL
     ADDR = SHIPADDR,
    CITY = ShipCity,
    STATE = ShipSTATE
  ELSE
    ADDR = ADDR,
    CITY = City,
    STATE = STATE
   )
   
FROM Table_1





This is a trunicated version, the actual query is a few pages so it would be hepful if i can write it as above
LVL 15
Ess KayEntrapenuerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
SELECT ID, 
   CASE WHEN ShipAddr IS NOT NULL THEN ShipAddr ELSE ADDR END as ADDR, 
   CASE WHEN ShipAddr IS NOT NULL THEN ShipCity ELSE City END as CITY,
   CASE WHEN ShipAddr IS NOT NULL THEN ShipSTATE ELSE STATE END as STATE
FROM Table_1

Open in new window

 
Check out my article called SQL Server CASE Solutions for some more colorful examples of using CASE blocks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT ID, 
  ISNULL(SHIPPADDR,ADDR) AS SHIPADDR,
  ISNULL(ShipCity, CITY) AS ShipCity,
  ISNULL(ShipSTATE, STATE) AS ShipSTATE
 FROM Table_1

Open in new window

Ess KayEntrapenuerAuthor Commented:
thanks, thatws what i wrote, i just wanted to know if there is a less wordy way like i did above in pseudocode
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Jim HornMicrosoft SQL Server Data DudeCommented:
Vitor's solution works as well if you're trying to get a lower read on the wordy-o-meter.
Ess KayEntrapenuerAuthor Commented:
Negative,

i have addr1 and addr2

if addr2ship is blank, it will take addr2,
and that is just wrong
same with city state, i need the entire group not just one field


example,
Mail:

123 Goo ST
APT 7
Happyville, GA, 99999


SHIP addr:

321 Ship Drive
NULL
PleasentVille, MD


His result would be
321 Ship Drive
APT 7
PleasentVille, MD, 99999
Jim HornMicrosoft SQL Server Data DudeCommented:
>if addr2ship is blank, it will take addr2  and that is just wrong
Then your initial requirements were wrong, as they stated that (IF SHIPPADDR ISNOT NULL was the only criteria for all columns.

If you wish to change the criteria to a field by field comparison (i.e. ShipAddr to ADDR, ShipCity to City, etc.) then you can change either of our codes to instead of always evaluate SHIPADDR, evaluate ShipADDR, ShipCity, and ShipState.
Jim HornMicrosoft SQL Server Data DudeCommented:
>i have addr1 and addr2
either add Addr in a similar way as the other columns, or if you wish to add it to the addr1 line...

Using CASE
   CASE WHEN ShipAddr IS NOT NULL THEN ShipAddr ELSE ADDR END + CASE WHEN ShipAddr2 IS NOT NULL THEN ', ' + ShipAddr2 ELSE '' END as ADDR, 

Open in new window

Ess KayEntrapenuerAuthor Commented:
Jim, Looks like we went off a tangent here.
You had the correct idea, which is what im using


CASE WHEN M.MemberMailingAddress1 IS NOT NULL THEN M.MemberMailingAddress1 + ' '+ISNULL(M.MemberMailingAddress2, ' ') 
ELSE  ISNULL(M.MemberAddress1,'')+ ' '+ISNULL(M.MemberAddress2, ' ')	END AS MemberAddress,
CASE WHEN M.MemberMailingAddress1 IS NOT NULL THEN M.MemberMailingCity ELSE  M.MemberCity	END AS City,
CASE WHEN M.MemberMailingAddress1 IS NOT NULL THEN M.MemberMailingState ELSE  M.MemberState	END AS State,
CASE WHEN M.MemberMailingAddress1 IS NOT NULL THEN M.MemberMailingZip ELSE  M.MemberZip	END AS Zip,		...etc

Open in new window



I was just wondering if there is a way i can use a single IF statement instead of a CASE for each field


(As far as Victor's answer, it would evaluate each field individually, hence why it is incorrect)
Jim HornMicrosoft SQL Server Data DudeCommented:
>I was just wondering if there is a way i can use a single IF statement instead of a CASE for each field
No, you can't use a single decision structure like IF to affect multiple columns.
Ess KayEntrapenuerAuthor Commented:
In a way, it can be done in several ways,
one is in a innerjoin to the table,



IF membershipaddr is null
 (select into #temp memberaddr....etc )
else
 (select into #temp membershipaddr....etc )
end

select (mainquery) innerjoin #temp


second is to IF the entire query



third is to place all in temp table, and update if ship is not null
IF membershipaddr is not null
 (update #temp memberaddr....etc )
end



Anyway, thanks for your thoughts.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Another solution is to use a UNION clause:
SELECT ID, SHIPADDR, ShipCity,ShipSTATE
FROM Table_1
WHERE SHIPPADDR IS NOT NULL
UNION
SELECT ID, ADDR, City, STATE
FROM Table_1
WHERE SHIPPADDR IS NULL

Open in new window

Ess KayEntrapenuerAuthor Commented:
yes good point.  regardless,  the actual query is huge,  including quantity of tables and records,  id rather just the if else block like i pseudocoded in the question,  least resource consuming approach is the one credited points
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.