SQL Return Specific Value for Duplicate rows
===

Here is my issue

I am using bulk import to import a CSV file into a table (InputTable). This table needs to be separated into 3 different tables with distinct values.

Tables are Client, Child Company, Contacts

Client has a one to many relationship to child company as well as to contacts.

The client Table has has two fields (name,status)

it is easy enough to pull in a distinct name from dbo.InputTable using this query...

        insert into Client
        (
	Name
        )
        select distinct Name
        from InputTable

this query will insert data like this

<pre>Name

One Company

Two Company

Three Company

</pre>

However when i try this code

        insert into Client
        (
	 Name
         ,Status

        )
        select distinct Name
        from InputTable
        group by Name
        ,Status

I get this result

<pre>Name         | Status

One Company       | Active

Two Company       | Active

Two Company       | Terminated

Three Company     | Active

</pre>


Here is the kicker, If client is showing active in one row no matter how many rows then i need to show active for that Name record on the Client Table

if they are showin all terminated then i would need to copy in the terminated status to the client table for that row.

any ideas on how to accomplish this?

Thank you in advance
 
