Exporting Data To Excel, with Jagged Columns due to dynamic data?

I have a bit of an interesting problem where I need to create an Excel data dump from some 开发者_StackOverflow社区data in the database, but rather than it being something simple I have some complex data to merge, below is an example of the data that I have to deal with.

TblGeneralInfo

RecordId|Record Name  |Date      |Cost
1       |Test Entry  | 1/1/2010 |2.0
2       |Test Entry2 | 1/1/2010 |20.25

TblRandomInfo

RecordId |QuestionName   | Answer
1        |Your Name?     | Bob
1        |Your Title?    | The Builder
2        |Favorite Movie | The Matrix
2        |Favorite Car   | Mustang

What I need in the end is the following

RecordId|Record Name |Date      |Cost  |              |            |              |
1      |Test Entry  | 1/1/2010 |2.0   |Your Name?    | Bob        | Your Title?  | The Builder
2      |Test Entry2 | 1/1/2010 |20.25 |Favorite Move | The Matrix | Favorite Car | Mustang

Now I'm using SQL Server 2005 for the reporting and ASP.NET 3.5 (C#) for the application. I'm looking for the best way to do this. The number of items in "tblRandomInfo" is variable, and can have as many as 20-30 Q/A pairs per record. I cannot easily do a pivot, even a dynamic pivot due to the nature of the data.

Edit

A solution is valid either on the SQL Server or C# side. But note that information in tblRandomInfo can contain line breaks.

Is the issue that you are trying to create a T-SQL query that will make a table like your desired output?

I'm sure it can be done, but why not create the output in the c# application? The easiest thing is to write out a csv.

1. pull TblGeneralInfo records
2. pull TblRandomInfo records
3. for each record in TblGeneralInfo write RecordId, RecordName, Date, and Cost
4. for each record in TblRandomInfo that has a RecordId matching the TbGeneralInfo record, write QuestionName and Answer
5. newline

Of course, you can do this with interop, as well, as long as you have Excel installed on the server, but that adds complexity with rather little benefit over the csv.

I would probably go about this like so: Add the three fields matching an ID in TblGeneralInfo to a list, then add the fields for each matching ID in TblRandomInfo to that list. Add each value in this list to a row in your spreadsheet, rinse and repeat for each unique ID. You can even find a lib that makes this possible here: http://www.codeproject.com/KB/office/excelxmllibrary.aspx

I agree with Jay... A double foreach in code gives you the most control and isn't that hard to achieve.

But you said the data was jagged, so I have to assume that the "random info" table doesn't contain a fully predictable set of rows for each user. It isn't exactly a clean "pivot", so doing it in TSQL would be unnecessarily difficult and probably brittle if the data isn't always clean.

Here is an example using Linq to SQL (not checked for syntax or anything, so it may be a bit of a rough example). It assumes you have a data context called "ctx".

var someDate = DateTime.Now.AddMonths(-1);
var genInfo = ctx.TblGeneralInfos.Where(g => g.Date > someDate);

//old-fashioned datatable seems to fit our needs well for storing the output data.
var scratchTable = new DataTable();
scratchTable.Columns.Add("RecordId");//repeat for any other fields in General Info we need to output

//do one pass through data get the field names from random info and add each possibly column to scratch table
foreach (var g in genInfo)
{                
    foreach(var r in g.TblRandomInfos)
    {
        if(scratchTable.Columns.Contains(r.QuestionName)
        {
            scratchTable.Columns.Add(r.QuestionName);
        }
    }
}

//now that scratch table has every column we could need, read and populate from data
foreach (var g in genInfo)
{       
    var row = scratchTable.NewRow();
    row["RecordId"] = g.RecordId.ToString();
    foreach(var r in g.TblRandomInfos)
    {
        row[r.QuestionName] = r.Answer;
    }
    scratchTable.Rows.Add(row);// put row in scratch table
}

//Now all you have to do is convert datatable to csv... this is well documented online
//  http://www.dotnetspark.com/kb/436-convert-datatable-to-csv-file-using-c-sharp.aspx