title: SQL Server - When selecting how to propagate values on the top level of a tree? (SQL Fiddle included)
body: <p>I need to propagate values on top level of tree:</p>
  <pre><code>CREATE TABLE [dbo].[area](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [name] [varchar](50) NOT NULL,
      [parent_id] [int] NULL,
      [color] [varchar](50) NULL,
      [priority] [int] NULL)
  
  insert into area values('root', NULL, NULL, NULL);
  insert into area values('test1', 1, NULL, NULL);
  insert into area values('test2', 2, 'red', 50);
  insert into area values('test3', 5, 'blue', 1);
  insert into area values('test4', 1, 'yellow', 10);
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>parent_id</th>
  <th>name</th>
  <th>color</th>
  <th>priority</th>
  <th>level</th>
  <th>path_id</th>
  <th>path_name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>(null)</td>
  <td>root</td>
  <td>(null)</td>
  <td>(null)</td>
  <td>0</td>
  <td>1</td>
  <td>root</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>test1</td>
  <td>(null)</td>
  <td>(null)</td>
  <td>1</td>
  <td>1\2</td>
  <td>root\test1</td>
  </tr>
  <tr>
  <td>5</td>
  <td>1</td>
  <td>test4</td>
  <td>yellow</td>
  <td>10</td>
  <td>1</td>
  <td>1\5</td>
  <td>root\test4</td>
  </tr>
  <tr>
  <td>4</td>
  <td>5</td>
  <td>test3</td>
  <td>blue</td>
  <td>1</td>
  <td>2</td>
  <td>1\5\4</td>
  <td>root\test4\test3</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  <td>test2</td>
  <td>red</td>
  <td>50</td>
  <td>2</td>
  <td>1\2\3</td>
  <td>root\test1\test2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I am doing this with the following query (with CTE for next process):</p>
  <pre><code>WITH tPATH
  AS (SELECT id,
             parent_id,
             name,
             color,
             priority,
             0 as [level],
             CAST(id AS NVARCHAR(MAX)) As path_id,
             CAST(name AS NVARCHAR(MAX)) As path_name
      FROM area
      WHERE parent_id is NULL
      UNION ALL
      SELECT area.id,
             area.parent_id,
             area.name,
             area.color,
             area.priority,
             [level] + 1,
             CONCAT(tPATH.path_id, '\', CAST(area.id AS NVARCHAR(MAX))),
             CONCAT(tPATH.path_name, '\', CAST(area.name AS NVARCHAR(MAX)))
      FROM area
          INNER JOIN tPATH
              ON area.parent_id = tPATH.id
     )
  select *
  from tPATH;
  </code></pre>
  <p>Priority 1 is the best. So I want this:</p>
  <ul>
  <li>blue must be in id 1 with priority 1,</li>
  <li>red must be in id 2 with priority 50,</li>
  <li>blue must be in id 5  with priority 1,</li>
  <li>id 4 and 3 remain unchanged.</li>
  </ul>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>parent_id</th>
  <th>name</th>
  <th>color</th>
  <th>priority</th>
  <th>level</th>
  <th>path_id</th>
  <th>path_name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>(null)</td>
  <td>root</td>
  <td>blue</td>
  <td>1</td>
  <td>0</td>
  <td>1</td>
  <td>root</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>test1</td>
  <td>red</td>
  <td>50</td>
  <td>1</td>
  <td>1\2</td>
  <td>root\test1</td>
  </tr>
  <tr>
  <td>5</td>
  <td>1</td>
  <td>test4</td>
  <td>blue</td>
  <td>1</td>
  <td>1</td>
  <td>1\5</td>
  <td>root\test4</td>
  </tr>
  <tr>
  <td>4</td>
  <td>5</td>
  <td>test3</td>
  <td>blue</td>
  <td>1</td>
  <td>2</td>
  <td>1\5\4</td>
  <td>root\test4\test3</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  <td>test2</td>
  <td>red</td>
  <td>50</td>
  <td>2</td>
  <td>1\2\3</td>
  <td>root\test1\test2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Any idea to do that (propagate color and priority on top level)? <a href="http://sqlfiddle.com/#!18/01287/17" rel="nofollow noreferrer">The fiddle</a> || <a href="https://sqlize.online/sql/mssql2019/ca2ddda67f6dcf4f589aebad858ed9c1/" rel="nofollow noreferrer">SQLize</a></p>
  <p>Thanks in advance.</p>