asked
Query to create crosstab report
I need help in following. I have not tried anything because I have applied a naive approach, hardcoding elements. However as pivots are increasing in numbers I have to provide support on every change, I would like to change code so that changes will be dynamically supported without my intervention. To me this is a complex query and I have absolutely no idea how to go about it. I would need to make variations once I get to understand the concept. I have (among others) 2 tables: table1 taxtype fields type taxid integer primary unique taxdesc varchar taxpc decimal(10,2) table2 billdetails This is detail table of a master table: fields type billid forgein key [billid] from master table taxid integer same as taxid in taxtyp quantity integer unitpric decimal(10,2) totalprice decimal(10, 2) basically it is quantity * unitpric What I have to do is calculate: - billwise - datewise -- date will be picked up from master table - monthwise following value taxamount = it will be for all the records in taxtype so that I have a crosstab report having following columns: 1. Description - TaxType description 2. Total Amount - Total amount including tax 3. Tax Amount - VAT like tax amount component of Total Amount 4. Pre tax Amount I need help in building query - could be stored proc or views . A template query will do and I can adapt it for my purposes. Even an example done earlier will do - I will try and understand it. Thanks in advance
Ninety percent of the time you're going to want to do the pivoting on the client tool (for example Excel pivot table or SSRS matrix). In this case it's not clear whether you're wanting SQL Server to do the pivoting (cross tabbing - call it what you will) or the client tools. If you create pivoted data in SQL Server you're going to to take away all the flexibility of allowing it to be done with client tools. Is this what you really want? Can you post some sample source data (in the format of the source tables) and a screenshot of a sample report that you want to produce? It's probably not a complicated question. It's just a little unclear at present.
19 People are following this question.
