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

more ▼

asked Jan 08, 2016 at 02:47 AM in Default

avatar image

11 1

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.

Jan 09, 2016 at 07:15 AM GPO

Also break it down into little bits, there must be bits that you understand how to do. So rather than ask for everything, if you do a bit yourself you will find people more willing to help

Jan 11, 2016 at 08:49 AM Gazz
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 08, 2016 at 02:47 AM

Seen: 107 times

Last Updated: Jan 13, 2016 at 10:59 AM

Copyright 2018 Redgate Software. Privacy Policy