title: SQL Script To Dynamically Coalesce Multiple and Disparately Named Fields Across Multiple DB's
body: <p>I am attempting to write a single SQL Server script (for Reporting Services) that can run against multiple databases (Reporting Services will determine the DB to run against).  The problem is that I have one table in DB that can vary from database to database and change in the number and name of columns.  Here is an example of the table that could change from DB to DB. </p>
  
  <p><strong>Database 1:</strong></p>
  
  <ul>
  <li>IDField</li>
  <li>FieldA</li>
  <li>FieldB</li>
  </ul>
  
  <p><strong>Database 2:</strong></p>
  
  <ul>
  <li>IDField</li>
  <li>FieldX</li>
  <li>FieldY</li>
  <li>FieldX</li>
  </ul>
  
  <p>I now want to write a script that returns the values of the arbitrary fields and coalesce them into one text value (so that Reporting Services only needs to be designed to have a single column for these fields).  Here's what I want the output to look like:</p>
  
  <p><strong>When run on Database 1:</strong></p>
  
  <p>"IDFieldValue" | "FieldA:value, FieldB:value"</p>
  
  <p><strong>When run on Database 2:</strong></p>
  
  <p>"IDFieldValue" | "FieldX:value, FieldY:value, FieldZ:value"</p>
  
  <p>I know I could do this with a cursor, but that's very resource and time intensive.  I was hoping to do this with straight SQL.  Any thoughts as to how?</p>