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
精彩评论