title: SQL - Converting multiple rows into multiple columns
body: <p>Need a simple help here. Have went though various solutions such as using case and row number, pivot but unable to find a suitable solution.</p>
  
  <p>Basically I want to use the group the Name values and find all the non null and distinct value and display the results in a single row. </p>
  
  <p>Input </p>
  
  <pre><code>Name | Color
  John | Blue
  John | Green
  Mary | NULL
  Mary | Yellow
  Mary | Pink
  Mary | Pink
  </code></pre>
  
  <p>Expected Output</p>
  
  <pre><code>Name | Color1 | Color2 | Color3
  John | Blue   | Green  |  NULL
  Mary | Yellow | Pink   |  NULL
  </code></pre>
  
  <p>Currently I've came up to the query below but still looks far from my desired outcome. Appreciate your assistance , thanks!</p>
  
  <pre><code>select  Name,
      max(case when seqnum = 1 then Color end) as Color1,
      max(case when seqnum = 2 then Color end) as Color2,
      max(case when seqnum = 3 then Color end) as Color3
  from (select table.*, row_number() over (partition by Name order by Name) as seqnum
      from table
      ) table
  group by Name
  </code></pre>