January 30, 2013 at 10:07 am
TableA
Key AgencyFrom ValueFrom AgencyTo ValueTo
---------------------------------------------------
223 <blank> 300 washington 200
TableA has the above mentioned columns. I want to derive either ValueFrom or ValueTO only if AgencyFrom or AgencyTo is <blank>
How do it do that? Below is my script but it returns something like this.
SELECT
CASE WHEN AgencyFrom = '' then ValueFrom
Else '' End as From
CASE WHEN AgencyTo = '' then ValueTo
Else '' End as To
From TableA
Where Code = 223
Output:
ValueFrom ValueTo
-------------------------------
300
Desired Output
ValueFrom
----------
300
January 30, 2013 at 10:48 am
To clarify. You're looking for only a one column output? What you're describing sounds like two separate queries, one for blank AgencyFrom and another for blank AgencyTo.
January 30, 2013 at 11:09 am
Yes. I want only one column as the output. And the condition for the output is the <blank> value in one of the columns. In my table, only one column (AgencyFrom or AgencyTo) hold <blank> data per row. Both do not hold <blank> together.
Does this make sense?
January 30, 2013 at 1:27 pm
Ok, here's one possibility. I'm still not sure that I understand the requirements but I believe this will get the type of output you're looking for. Note that it requires a consistent name for the final output column.
A suggestion. To get the best help, you'll do better posting ddl for the tables you need and sample data to insert. That saves folks time when they're trying to provide help. It also helps clarify what you're looking for. See the article http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url] about best practices for getting help on the forums.
Create Table TableA
(
PK int primary key clustered,
AgencyFrom varchar(30),
ValueFrom int,
AgencyTo varchar(30),
ValueTo int
)
;
Insert TableA (PK, AgencyFrom, ValueFrom, AgencyTo, ValueTo)
Values
(223, '', 300, 'Washington', 200),
(230, 'Boston', 400, '', 100)
;
With blankFrom
As
(
Select
ValueFrom as Value
From
TableA
Where
AgencyFrom = ''
),
blankTo
As
(
Select
ValueTo as Value
From
TableA
Where
AgencyTo = ''
)
Select Value
From blankFrom
Union
Select Value
From blankTo
;
Drop table TableA;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply