title: Four columns based on four keys
body: <p>I made a table first based on information that consists of 5 numbers and a year (+B sometimes):</p>
  
  <pre><code>SELECT  number5,    
  YEAR(MyDate) AS [year], 
              concat(a.[number5],'-',YEAR(MyDate)) AS [A1],  
              concat(a.[number5],'-',YEAR(MyDate)-1) AS [A1-1],
              concat(a.[number5], '-',YEAR([MyDate]),'B') AS [B1],
              concat(a.[number5],'-',YEAR(MyDate)-1,'B') AS [B1-1]
  
   INTO #temp
   FROM aTable
  </code></pre>
  
  <p>So you would get:</p>
  
  <pre><code>number5 year   A1          A1-1         B1           B1-1
  12345   2001   12345-2001  12345-2000   12345-2001B  12345-2000B   
  23456   2002   23456-2002  23456-2001   23456-2002B  23456-2001B
  34569   2003   34569-2003  34569-2002   34569-2003B  34569-2002B
  45678   2004   45678-2004  45678-2003   45678-2004B  45678-2003B
  </code></pre>
  
  <p>Then I join this table with other information:</p>
  
  <pre><code>FileName        Value    Brand
  12345-2001      10000    Apple
  23456-2002      30409    Microsoft
  34569-2003      09283    Microsoft
  12345-2001B     20398    Apple
  45678-2003B     20384    Apple
  
  SELECT * FROM OtherTable AS a LEFT JOIN #temp AS b ON
  b.A1 = a.Filename
  </code></pre>
  
  <p>However, this gives values for one row and NULL values for another row. How can I program it in a way that it understands that if I join the FileName with one of the columns of A1, A1-1,B1 and B1-1, that it also puts the information in new columns. So:</p>
  
  <pre><code>Numb5   year   A1            A1-1           B1          B1-1        FileN. Value     Brand    FileN. Value Brand     FileN.        Value  Brand.
  12345   2001   12345-2001  12345-2000   12345-2001B  12345-2000B 12345-2001 10000    Apple                        12345-2001B     20398    Apple
  </code></pre>
  
  <p>I already tried a self join (but maybe wrongly):</p>
  
  <pre><code>WITH RLT AS (
  SELECT *, Row_number() over (PARTITION BY [FileName] ORDER BY [year] Desc) RN FROM OtherTable)
  </code></pre>
  
  <p>So what I want is that it looks up the Value in row 1 A1 which is: 12345-2001 and returns the values of:
  FileName Value Brand</p>
  
  <p>And then looks up the second value in the same row which is: 12345-2000 and return the values of:
  FileName Value Brand. </p>
  
  <p>However, I want them to be next to each other, so:</p>
  
  <pre><code>A1   A1-1 B1  B1-1  FileName (A1)  Value (A1)  Brand (A1)  FileName (A1-1) Value (A1-1) Brand (A1-1)  FileName (B1) Value (B1) Brand (B1) FileName (B1-1) Value (B1-1) Brand (B1-1). 
  </code></pre>