title: Snowflake aggregating table by a column
body: <p>I have a snowflake table that looks like below:</p>
  <p><a href="https://i.stack.imgur.com/eRXN0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eRXN0.png" alt="enter image description here" /></a></p>
  <p>I want to have all the rows for NEW_COL captured by Name as below.</p>
  <ol>
  <li>The table needs to be sorted by Code</li>
  <li>Keep 1st row grouped by 'Name' column , capturing all the values of NEW_COL.</li>
  </ol>
  <p><strong>EXPECTED OUTPUT</strong></p>
  <p><a href="https://i.stack.imgur.com/hVMK5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hVMK5.png" alt="enter image description here" /></a></p>
  <p>I have tried below code but not getting the combined value of NEW_COL, it just gets the {&quot;Code&quot; : &quot;A&quot;, &quot;Dept&quot; : &quot;Dept Store&quot;} for row 1 whereas I should get {&quot;Code&quot; : &quot;A&quot;, &quot;Dept&quot; : &quot;Dept Store&quot;},                            {&quot;Code&quot; : &quot;B&quot;, &quot;Dept&quot; : &quot;All other supplies&quot;},                  {&quot;Code&quot; : &quot;C&quot;, &quot;Dept&quot; : &quot;Rest&quot;} for the 1st row</p>
  <pre><code>select *, row_number() over (partition by Name order by Code) 
  as row_number 
  from TEST where row_number = 1
  </code></pre>
  <p>Here is what I am getting with my code</p>
  <p><a href="https://i.stack.imgur.com/YHLAf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YHLAf.png" alt="enter image description here" /></a></p>