'Rolling up' records in triplicate, nested case statement (or loops)

Question

I have a table with four columns: ID, OUTPUT, NOTE_TEXT, SOURCE

The ID values are in triplicate since I pulled the date from three different SOURCEs with a union all (a union won't work, which sucks). Effectively triplicating each record.

I need to roll these triplicate ID values up into one record per ID.

Below is a screenshot of the four variations of data for NOTE_TEXT values per ID triplicates:

Table

Here's an example output from the query below:

Output

As is evident from ID = 00793, the below case statement didn't work. Why is that? As well, can it be modified to work, or should I build a series of loops, or is there an even more elegant solution?

The end goal is to save this as a stored procedure and call into an SSIS project. Any insight or advice is appreciated.

select * from 
(
select 
ID
,OUTPUT
,case
    when NOTE_TEXT = '' then case
                                 when [SOURCE] = 'G1' and NOTE_TEXT != '' then NOTE_TEXT
                                 when [SOURCE] = 'G2' and NOTE_TEXT != '' then NOTE_TEXT
                                 when [SOURCE] = 'ENG' and NOTE_TEXT != '' then NOTE_TEXT
                                 else ''
                                 end
    else NOTE_TEXT
    end as NOTE_TEXT
from Table
) as temp
group by ID, OUTPUT, NOTE_TEXT

Edit: I may have been unclear, this is the logic I'd like to build into the query, using the existing case statement or any other way.

• If all three ID records have NULL in the NOTE_TEXT column, use one (or the first) ID record with NULL for NOTE_TEXT
• If all three ID records have the same text in the NOTE_TEXT column, use one (or the first) ID record with the text in NOTE_TEXT
• If, in the three ID records, the NOTE_TEXT column has one text value and the rest are NULL, use one (or the first) ID record and the text in NOTE_TEXT
• If, in the three ID records, the NOTE_TEXT column has multiple text values, use the the first ID record and NOTE_TEXT for a single record.

Answer 1

Using case statements wasn't going to work, based on the @Habo's comment. Instead, this worked.

select distinct(ID) AS ID
,'' AS OUTPUT_TYPE
,       (
       select top 1
           NOTE_TEXT
       from [table] AS t
       where NOTE_TEXT is not null
             and NOTE_TEXT != ''
             and t.SUPPLIER_ID = s.SUPPLIER_ID
   ) as NOTE_TEXT
from [table] as s