title: SSRS Multiple Lines of data
body: <p>My SQL Server 2008 report returns me multiple lines in a table.  A simple select statement returns two columns, one is the record number the other is a sector.  The sector column can contain any one of 6 different values.</p>
  
  <p>EDIT: <code>NULL</code> values are allowed in the sector column.</p>
  
  <p>I want this data to be in one line in my table.</p>
  
  <p>Lets say record number 1 has Sector A, Sector C and Sector E
  and Record 2 has Sector B and Sector C
  And Record 3 has none.</p>
  
  <p>I am after three lines of data.  I also need it displayed two ways.  One so that all sectors appear in the same cell separated by commas.  The other is a separate Cell for each category</p>
  
  <pre><code>Record number  | Sector
  1              | A, C, E
  2              | B, C
  3              | 
  </code></pre>
  
  <p>Or</p>
  
  <pre><code>Record Number | Sector A | Sector B | Sector C | Sector D | Sector E
  1             |  A       |          | C        |          | E
  2             |          | B        | C        |          | 
  3             |          |          |          |          | 
  </code></pre>
  
  <p>At the moment my report gives me 6 rows.</p>
  
  <pre><code>Record Number | Sector
  1             | A
  1             | C
  1             | E
  2             | B
  2             | C
  3             | 
  </code></pre>
  
  <p>Is there a way of working around this problem?</p>
  
  <p>I am using the query designer rather than writing the SQL statements.</p>