T-SQL Pivot - Pivot on all rows programmatically

Question

Situation

I have a report which is to be export by customers to excel format when they please that must meet certain formatting criteria.

Problem

This report is a statutory return that must be returned in a specific format. I have decided this can be achieved with the use of pivots. Instead of a list view they require a grid of totals, like this

enter image description here o...

This can be done manually by writing out lots and lots of pivots in a big complex statement, but would need to be updated as soon as the column I am pivoting on has a new entry, which will be frequent.

Perfect Solution

The perfect solution for this would be for somebody to find something magical that automatically pivots on all data in one column and does so programmatically, so if entry's are added to that column, they will also be pivoted on.

I am not looking for exact code here because that's lazy and I won't learn anything. A shove in the right direction would be nice though, if this is at all possible.

You can do this with dynamic SQL ([Example in this answer](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query)). How are you delivering the reports? SSRS for example can build matrices that automatically pivot akin to an excel pivot table, this would be better than using dynamic SQL to export the data. — GarethD, Sep 20 '13 at 09:16
The reports are generated via a stored procedure and a little windows form that executes the sproc and saves it in excel format for the user. SSRS is really out of the question. Crystal is an option but customer convinence is a real priority here. — Alec., Sep 20 '13 at 09:19
You can embed reports in windows forms applications, which gives the option of just viewing the report, or exporting in a number of formats. If the pivot needs to be done in SQL then dynamic sql is the only way to do it. — GarethD, Sep 20 '13 at 09:27

score 2 · Accepted Answer · edited May 23 '17 at 12:30

You can do this with dynamic SQL (Example in this answer). Although this is frequently best achieved through an application/reporting layer rather than directly in SQL.

In your case you can embed reports in Windows Forms Applications, which gives the option of just viewing the report, or exporting in a number of formats. If the pivot needs to be done in SQL then dynamic sql is the only way to do it.

T-SQL Pivot - Pivot on all rows programmatically

1 Answers1