title: access sql query horizontal to vertical
body: <p>I have a mapping table </p>
  
  <pre><code>EK_ID   CS      EK_ID-CS    
  1001    1071    1001_1071   
  1001    1015    1001_1015
  1004    1061    1004_1061
  1004    1007    1004_1007
  1004    1006    1004_1006
  1004    1005    1004_1005
  1005    1011    1005_1011
  1005    1010    1005_1010
  1006    1013    1006_1013
  1006    1012    1006_1012
  </code></pre>
  
  <p>And Table B, which has common field EK_ID. </p>
  
  <pre><code>EK_ID   S1      S2        S3    S4
  1001    0.94    0.03    0.01    
  1004    0.96    8.12    38.10   0.06
  1005    1       2.30        
  1006    1       2.47        
  </code></pre>
  
  <p>Inner join till field S1 gives the result</p>
  
  <pre><code> EK_ID  CS     EK_ID-CS S1
  
  1001    1071    1001_1071   0.94
  1001    1015    1001_1015   0.94
  1004    1061    1004_1061   0.96
  1004    1007    1004_1007   0.96
  1004    1006    1004_1006   0.96
  1004    1005    1004_1005   0.96
  1005    1011    1005_1011   1
  1005    1010    1005_1010   1
  1006    1013    1006_1013   1
  1006    1012    1006_1012   1
  </code></pre>
  
  <p>I'm trying to write columns S2,S3,S4 values in table B as one column S2 , vertical like below</p>
  
  <pre><code> EK_ID  CS        EK_ID-CS  S1       S2
  1001    1071    1001_1071   0.94    0.03
  1001    1015    1001_1015   0.94    0.01
  1004    1061    1004_1061   0.96    
  1004    1007    1004_1007   0.96    8.12
  1004    1006    1004_1006   0.96    38.10
  1004    1005    1004_1005   0.96    0.06
  1005    1011    1005_1011   1      2.30
  1005    1010    1005_1010   1   
  1006    1013    1006_1013   1      2.47
  1006    1012    1006_1012   1   
  </code></pre>
  
  <p>How do I represent in a access sql query?</p>