title: TSQL - View with cross apply and pivot
body: <p>this is my base table:</p>
  
  <pre><code>docID |  rowNumber | Column1 |  Column2 | Column3
  </code></pre>
  
  <p>I use cross apply and pivot to transform the records in Column1 to actual columns and use the values in column2 and column3 as records for the new columns. In my <a href="http://sqlfiddle.com/#!6/fd4ab/1" rel="nofollow noreferrer">fiddle</a> you can see base and transformed select statement.</p>
  
  <p>I have columns like Plant and Color which are numbered, e.g. Plant1, Plant2, Plant3, Color1, Color2 etc.</p>
  
  <p>For each plant that exists in all plant columns I want to create a new row with a comma separated list of colors in one single column.</p>
  
  <p>What I want to achieve is also in below screenshot:</p>
  
  <p><img src="https://i.stack.imgur.com/XeJp7.png" alt="input-output"></p>
  
  <p>This should become a view to use in Excel. How do I need to modify the view to get to the desired result?</p>
  
  <p>Additional question: The Length-column is numeric. Is there any way to switch the decimal separator from within Excel as a user and apply it to this or all numeric column(s) so that it will be recognized by Excel as a number?
  I used to have an old php web query where I would pass the separator from a dropdown cell in Excel as a parameter.</p>
  
  <p>Thank you.</p>