开发者

T-SQL Select Data if both null and data are returned, else null

I have a t-sql report that results in the following output:

开发者_JS百科
     A       B             C
Bridgevine  6093690444  3917018
Bridgevine  972         3929277
Bridgevine  974 
Bridgevine  974         3918929
CSPP Test   72720110438 
CSPP Test   1234    
CSPP Test   CCDSTSTNW   
Intelisys   12114 - DD  
Intelisys   12114 - DD  3844758
Intelisys   12553 - TL  3881365

The problem is (as an example) the two records showing where B = 974. I would only like the report to show where C has a value. If a record has no value for C, I would like it to still show that record. So here's the desired output:

     A       B             C
Bridgevine  6093690444  3917018
Bridgevine  972         3929277
Bridgevine  974         3918929
CSPP Test   72720110438 
CSPP Test   1234    
CSPP Test   CCDSTSTNW       
Intelisys   12114 - DD  3844758
Intelisys   12553 - TL  3881365

I know this shouldn't be hard but I can't seem to figure it out. Would you use a COALESCE or CASE statement, or something else?


Probably an aggregate if you don't have records where A and B are the same, but C has different non-Null values

SELECT
  A, B, MAX(C)
FROM
 whatever
GROUP BY
  A, B
ORDER BY
 whatever

So this fails for data like

CSPP Test   SomeB    null
CSPP Test   SomeB    FirstC
CSPP Test   SomeB    SecondC    --only get this row

or

Intelisys   AnotherB    ValidC1
Intelisys   AnotherB    ValidC2    --only get this row


SELECT ...
FROM table_1 a WHERE a.C is NOT NULL OR 
(SELECT COUNT(*) FROM table_1 a1 WHERE a1.B = a.B)=1
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜