SQL Script To Dynamically Coalesce Multiple and Disparately Named Fields Across Multiple DB's

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.

Database 1:

• IDField
• FieldA
• FieldB

Database 2:

• IDField
• FieldX
• FieldY
• FieldX

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:

When run on Database 1:

"IDFieldValue" | "FieldA:value, FieldB:value"

When run on Database 2:

"IDFieldValue" | "FieldX:value, FieldY:value, FieldZ:value"

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?

This will be pretty close to what you're looking for. It uses dynamic sql.

I only spent enough time to provide the concept, but I think once you look it over you'll see where you can modify in order to format just like you want.

Hint: replace YOURTABLENAME with the actual name of the table your running against.

declare @pQuery varchar(max)
declare @pFields varchar(max)

SELECT @pFields = COALESCE(@pFields + '''' + '  ' +column_name + ': ''+ ', '') + 'Cast(' + column_name + ' AS VarChar(max)) + ' 
FROM information_schema.columns WHERE table_name = 'YOURTABLENAME';

SELECT @pQuery = 'SELECT ' + @pFields + '''' + '''' + ' AS [ReportRow] FROM YOURTABLENAME';

SELECT @pQuery  /** just to see what you've created */

exec(@pQuery);  /** execute dynamic sql and get back concatenated rows */

This script runs on a single table, but once you see the approach it wouldn't be too much more work to modify the script to build your dynamic sql statement against many tables.

In response to the question in comments:

Don't modify the first select clause. Modify the where clause of the first select statement, and the second select statement (assigning to @pQuery)

SELECT @pFields = COALESCE(@pFields + '''' + '  ' +column_name + ': ''+ ', '') + 'Cast(' + column_name + ' AS VarChar(max)) + ' 
FROM information_schema.columns WHERE table_name = 'YOURTABLENAME'
AND column_name <> 'IDField' ;

SELECT @pQuery = 'SELECT IDField,  ' + @pFields + '''' + '''' + ' AS [ReportRow] FROM YOURTABLENAME';

Hopefully your ID columns are all named the same. If not, you'll have to select from information_schema where table_name = 'yourtablename' and ordinal_position = 1 into a variable and use that in place of the string literals above. In this case, hopefully your ID field is the first field in the table :)