title: SQL Creating multiple columns in View from one single table-column
body: <p>I'm trying to create a View in SQL Server 2008 R2, where data is extracted from two tables with dual one-to-one relationships, and I want to create two columns in the view based values from a single column in one of the tables.</p>
  
  <p>The tables are currently similar to these examples:</p>
  
  <p>TableA:</p>
  
  <pre><code>PrimaryKey | Name   | Value_FK | Number_FK
  -------------------------------------------
  66         | NameA  | 1         | 2
  77         | NameB  | 3         | 4
  </code></pre>
  
  <p>TableB:</p>
  
  <pre><code>PrimaryKey | Value   
  -------------------
  1          | 238
  2          | 456
  3          | 100
  4          | 200
  </code></pre>
  
  <p>The View should look like this:</p>
  
  <pre><code> Name   | Value | Number
  -------------------------
   NameA  | 238   | 456
   NameB  | 100   | 200
  </code></pre>
  
  <p>('Value' and 'Number' are essentially the same type, and are both found in the 'Value' column in TableB. I thought it would be more easy to distingiush between 'Value' and 'Number', than 'ValueA' and 'ValueB').</p>
  
  <p>The factor that should decide which values go into Column 'Value' or Column 'Number' is the PrimaryKey in TableB and its references in either foreignkey in TableA (but both FKs shall NEVER refer to the same key).</p>
  
  <p>This is likely not the most brilliant database model, having dual relationships between to tables. This is however due to mapping some C#.NET classes to a database by using ADO.NET Entity Framework, where Class A has two objects of Class B (in this case named 'Value' and 'Number'), and the database model currently constructs two relationships becuase of this. Changing this is not an option.</p>
  
  <p>I've tried googling this, but I find it difficult to find an answer that I need. Especially when most results are about the opposite: Selecting multiple columns into one column.</p>
  
  <p>So, how should I write the Select statement?</p>
  
  <pre><code>CREATE VIEW ViewName
  AS
  SELECT DISTINCT a.Name as 'Name', ????? as 'Value', ????? as 'Number'
  FROM TableA a, TableB b
  </code></pre>
  
  <p>I am quite rusty with advanced SQL-commands. It's been over 1,5 years since last I was into something this advanced. I tried something similar to this first:</p>
  
  <pre><code>CREATE VIEW ViewName
  AS
  WITH Name AS
  ( SELECT DISTINCT a.Name FROM TableA a  )
  
  Value AS
  (
      SELECT DISTINCT b.Value as 'Value' FROM TableA a, TableB b
  WHERE b.PrimaryKey = an.ValueA_FK
  ),
  Number AS
  (
      SELECT DISTINCT b.Value  as 'Number'
      FROM TableA a, TableB b
      WHERE a.PrimaryKey = an.ValueB_PrimaryKey
  )
  SELECT DISTINCT 
      * FROM Name, Value, Number
  </code></pre>
  
  <p>The result of my utterly failed attempt is like this:</p>
  
  <pre><code> Name   | Value | Number
  -------------------------
   NameA  | 100   | 200
   NameB  | 100   | 200
   NameA  | 100   | 456
   NameB  | 100   | 456
   NameA  | 238   | 200
   NameB  | 238   | 200
   NameA  | 238   | 456
   NameB  | 238   | 456
  </code></pre>
  
  <p>Now, any suggestiong as what to fill in the query?</p>