title: Crosstab/pivot query with Image data type in SQL Server 2008
body: <p>I need to execute a crosstab/pivot query in SQL Server using columns of all varchar data types and one image data type. I have created a stored procedure that joins a series of tables in this format. "B" the only element that can be linked to image data. If the DATA field for "B" is blank, then there is also blank IMAGEDATA for that row.</p>
  
  <pre><code>  OBJID    CONTAINERID    ELEMENT   DATA     IMAGEDATA   
    1        11             A         a123     NULL
    1        12             A         aa123    NULL
    1        11             B         b123     0XFFD8FFE       
    1        12             B         bb123    0XFFD8FFE       
    1        11             C         c123     NULL      
  </code></pre>
  
  <p>Then I use a pivot query. Ideally, the table should look like this:</p>
  
  <pre><code>  OBJID    CONTAINERID    A       B       C      IMAGEDATA   
    1        11             a123    b123    c123   0XFFD8FFE       
    1        12             aa123   bb123          0XFFD8FFE        
  </code></pre>
  
  <p>Problem is that image data fields cannot be pivoted, grouped, converted, etc...I've tried converting the image to varchar from binary, but it gives me gibberish.</p>
  
  <p>Restrictions: </p>
  
  <ol> 1. "ELEMENTS" are dynamic, so the number of columns for the pivot table is always changing. (ie. It *could* go all the way from A to Z.) </ol>
  
  <ol> 2. The fields are to be used in an SSRS report, therefore I need to maintain the image data type </ol>
  
  <ol> 3. I can't use any other developer language (.NET, etc..) </ol>
  
  <p>Is there any other possible way to do this?</p>