May 27, 2003 at 1:58 pm
I have a select statement similar to the following in Access...
SELECT A,B,C,
IIf(IsNull(D) = 0, D & ";", "") & IIf(IsNull(E) = 0, E & ";", "") & IIf(IsNull(F) = 0, F & ";", "") As JoinedClms,
G, H, I
The idea is to get fields A, B, C, G, H, I in seperate columns and then show fields D, E and F in a single column if they exist. If only one exists, only one is shown, if two exist, the two are shown, etc. It is the case where if the item in position "n plus 1" exists, then the items from 0 to n will also exist.
I am trying to convert this from Access to SQL Server 2000 and have come up with the following - (plus) is the plus symbol:
Select [A], , [C],
CASE [D]
When NULL Then
''
Else
D (plus) ';'
End
(plus)
Case [E]
When NULL Then
''
Else
[E] (plus) ';'
End
(plus)
Case[F]
When NULL Then
''
Else
[F] (plus) ';'
End
As JoinedClms,
[G],[H],
The problem I am having is that if only D and E contain data while F is NULL or if only D contains data while E and F are NULL, the field (JoinedClms) returns NULL. It is supposed to return either D;E or D in those two cases. However, it only returns the correct value (D;E;F) when all three fields are not NULL.
So, my question is...how do I get this query to behave in SQL Server like it does in Access?
Thanks much for the help.
May 28, 2003 at 3:19 am
This would be the query
SELECT
A, B, C,
(CASE WHEN D IS NULL THEN '' ELSE D + ';' END) +
(CASE WHEN E IS NULL THEN '' ELSE E + ';' END) +
(CASE WHEN F IS NULL THEN '' ELSE F + ';' END) As JoinedClms,
G, H, I
as for why IS NULL as opposed to the way you did take a look at this article I wrote on IS NULL versus = NULL.
http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply