title: Derive Column By Finding Unique String From Column in a Subset
body: <p>The idea behind this question is similar to this <a href="https://stackoverflow.com/questions/66870191/clean-data-using-sql-take-column-difference?noredirect=1#comment118206948_66870191">question</a> with a slight change. Consider data below</p>
  <p><strong>Actual Output</strong></p>
  <pre><code>+-------------+--------+------+
  |     Id      | Weight | Type |
  +-------------+--------+------+
  | 00011223344 |     35 | A    |
  | 00011223344 |     10 | A    |
  | 12311223344 |    100 | B    |
  | 00034343434 |     25 | A    |
  | 00034343434 |     25 | A    |
  | 99934343434 |    200 | C    |
  | 88855667788 |    100 | D    |
  +-------------+--------+------+
  </code></pre>
  <p><strong>Derived Output</strong></p>
  <pre><code>+-------------+-------------+--------+---------------+------+
  |     Id      |  Actual ID  | Weight | Actual Weight | Type |
  +-------------+-------------+--------+---------------+------+
  | 00011223344 | 12311223344 |     35 |            35 | A    |
  | 00011223344 | 12311223344 |     10 |            10 | A    |
  | 12311223344 | 12311223344 |    100 |            55 | B    |
  | 00034343434 | 99934343434 |     25 |            25 | A    |
  | 00034343434 | 99934343434 |     25 |            25 | A    |
  | 99934343434 | 99934343434 |    200 |           150 | C    |
  | 88855667788 | 88855667788 |    100 |           100 | D    |
  +-------------+-------------+--------+---------------+------+
  </code></pre>
  <p>I need to add another column to table above to add <code>Actual_Type</code> against column type. Above table can be derived by following code also provided <a href="https://stackoverflow.com/questions/66870191/clean-data-using-sql-take-column-difference?noredirect=1#comment118206948_66870191">here</a>.</p>
  <pre><code>select t.*,
         (case when id like '000%' then weight
               else weight - sum(case when id like '000%' then weight else 0 end) over (partition by actual_id)
          end) as actual_weight
  from (select t.*,
               max(id) over (partition by stuff(id, 1, 3, '')) as actual_id
        from t
       ) t;
  </code></pre>
  <p>I can't think of how can I apply partition by and apply max on a column Type that is varchar.</p>
  <p><strong>Desired Output</strong></p>
  <pre><code>+-------------+-------------+--------+---------------+------+-------------+
  |     Id      |  Actual ID  | Weight | Actual Weight | Type | Actual Type |   
  +-------------+-------------+--------+---------------+------+-------------+
  | 00011223344 | 12311223344 |     35 |            35 |  A   | B           |   
  | 00011223344 | 12311223344 |     10 |            10 |  A   | B           |   
  | 12311223344 | 12311223344 |    100 |            55 |  B   | B           |   
  | 00034343434 | 99934343434 |     25 |            25 |  A   | C           |   
  | 00034343434 | 99934343434 |     25 |            25 |  A   | C           |   
  | 99934343434 | 99934343434 |    200 |           150 |  C   | C           |
  | 88855667788 | 88855667788 |    100 |           100 |  D   | D           |
  +-------------+-------------+--------+---------------+------+-------------+
  </code></pre>
  <p><strong>Edit:</strong>
  I can not apply <code>min</code> or <code>max</code> over column <code>Type</code> which is <code>nvarchar</code>. I want to partition over column <code>Actual ID</code> and find the value which is unique in column <code>Type</code> i.e. <code>B</code> for 1st 3 rows. For next 3 it is C.</p>