<p>How do I get:</p>

<pre><code>id       Name       Value
1          A          4
1          B          8
2          C          9
</code></pre>

<p>to</p>

<pre><code>id          Column
1          A:4, B:8
2          C:9
</code></pre>

This kind of question is asked here very often, and the solution is going to depend a lot on the underlying requirements:
https://stackoverflow.com/search?q=sql+pivot
and
https://stackoverflow.com/search?q=sql+concatenate
Typically, there is no SQL-only way to do this without either dynamic sql, a user-defined function, or a cursor.