[edit]Turns out there's 1.5 million records... The SQL database is taking about 17-20 seconds to run the query. Would it be best to have Java handle the joins and rearranging in that case? Since this is a web app, if multiple users are getting Excel exports, would the long queries reduce the overall performance of the site?[/edit]I have a dataset that contains roughly 70,000 records. For simplicity's sake, let's say the data currently looks like this:User ID | Question | AnswerIn our web application, the data needs to be rotated 90 degrees and exported into Excel, such that we have the following:User ID | Question 1 | Question 2 | Question 3 .... I think we end up with around 50 or so columns.Should the data be rearranged on the database side using pivot tables or should it be rearranged on the application side?Which solution would be the easiest to maintain?Which solution would have the greatest performance?
Best practices for rotating large datasets?
design;database;web applications;sql
Since you are exporting data, in this situation I'd typically build the export file manually in code.  Pull back the rows sorted by user id, and loop through them.  While the dataset would be large, the query itself would be very simple and shouldnt stress the database much.  No need to otherwise burden the sql server with keeping the 'compiled' data set maintained when its not needed.