title: How to use PIVOT to separate values into different columns without duplication or NULL values
body: <p>I currently have the three tables below:</p>
  <p>Table <strong>#Name</strong></p>
  <pre><code>Name_id     NameValue
  ==========  =============
  50          Hannah
  51          Jeremy
  52          Luna 
  </code></pre>
  <p>Table <strong>#AttValue</strong></p>
  <pre><code>Name_id  AttributeValue_id  AttributeValue
  =======  =================  =============
  50       11                 01216892584
  50       12                 26
  50       13                 Female
  </code></pre>
  <p>Table <strong>#Attribute</strong></p>
  <pre><code>AttributeValue_id  AttributeName
  =================  =============
  11                 Phone Number
  12                 Age
  13                 Gender
  </code></pre>
  <p>What I want to do is combine them into one and this is what I have done so far:</p>
  <pre><code>SELECT NameValue,
  case when AttributeName = 'Phone Number' then AttributeValue end PhoneNumber
  ,case when AttributeName = 'Age' then AttributeValue end Age
  ,case when AttributeName = 'Gender' then AttributeValue end Gender
  FROM #Name A
  INNER JOIN #AttValue B ON A.Name_id = B.Name_id
  INNER JOIN #Attribute C ON B.AttributeValue_id = C.AttributeValue_id
  </code></pre>
  <p>This works fine however it results in multiple NameValues and NULL values which I am looking to avoid:</p>
  <pre><code>NameValue  PhoneNumber  Age  Gender
  =========  ============  ===  ======
  Hannah     01216892584   NULL NULL
  Hannah     NULL          26   NULL
  Hannah     NULL          NULL Female
  </code></pre>
  <p>The result I am looking for is:</p>
  <pre><code>NameValue  PhoneNumber  Age  Gender
  =========  ===========  ===  ======
  Hannah     01216892584  26   Female
  </code></pre>
  <p>I understand you can use PIVOT for this, however I struggle to figure out how to do it exactly.</p>