By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

SQL Syntax for aggregate crosstab query

TheSmileyCoder
Expert Mod 100+
P: 2,321
I will openly admit that SQL syntax is not my strongest feat in access.

I have a table of reviews tbl_Review with a autonumber ID field: KEY_Review, a date of creation dt_Created and a date of submittal, dt_Submittalwhich is null for reviews that have not yet been submitted.

I would like to create a crosstab query with the column headings being monthnames (jan, feb, mar....) and the row headers being "New Reviews", "Submitted Reviews","Total Open Reviews" and.
Now the "New Reviews" and "Submitted Reviews" I think I could do, but how do I make the "Total Open Reviews" row?

Example
Imagine that each month 3 reviews are opened, and only 2 submitted, the resulting query should look like this:
Expand|Select|Wrap|Line Numbers
  1.                     jan  feb  mar  apr  maj  jun  jul  aug  sep  okt  nov  dec  Total
  2. New Reviews           3    3    3    3    3    3    3    3    3    3    3    3    36
  3. Submitted Reviews     2    2    2    2    2    2    2    2    2    2    2    2    24
  4. Total Open Reviews    1    2    3    4    5    6    7    8    9   10   11   12    12
Dec 8 '11 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 446
Hi,
I shall watch what develops here with interest because I have never managed to make the cross-tab facility to work and format the way I want. I end up using multiple union queries.

S7
Dec 8 '11 #2

Rabbit
Expert Mod 10K+
P: 12,315
Normally, the total row is handled on the reporting side. Is there a reason you need it on the query side? I mean, it's possible, but makes for unyieldy code. Here's an example that works in SQL Server.
Expand|Select|Wrap|Line Numbers
  1. declare @t table (pk int identity, dtcreate datetime, dtsubmit datetime null);
  2.  
  3. insert into @t (dtcreate, dtsubmit) values ('01/01/2011', '01/02/2011');
  4. insert into @t (dtcreate, dtsubmit) values ('01/01/2011', '01/02/2011');
  5. insert into @t (dtcreate, dtsubmit) values ('01/01/2011', null);
  6. insert into @t (dtcreate, dtsubmit) values ('02/01/2011', '02/02/2011');
  7. insert into @t (dtcreate, dtsubmit) values ('02/01/2011', '02/02/2011');
  8. insert into @t (dtcreate, dtsubmit) values ('02/01/2011', null);
  9. insert into @t (dtcreate, dtsubmit) values ('03/01/2011', null);
  10. insert into @t (dtcreate, dtsubmit) values ('03/01/2011', '04/02/2011');
  11.  
  12. select category, [1], [2], [3], [4]
  13. from (
  14.     select ' new' as category, month(dtcreate) as d, count(*) as c
  15.     from @t as t
  16.     group by month(dtcreate)
  17.  
  18.     union all
  19.  
  20.     select ' review', month(dtsubmit), count(*)
  21.     from @t
  22.     where dtsubmit is not null
  23.     group by month(dtsubmit)
  24.  
  25.     union all
  26.  
  27.     select 'open', r1.m, isnull(r1.runcount, 0) - isnull(r2.runcount, 0)
  28.     from (
  29.         select m.m, sum(t2.c) as runcount
  30.         from (
  31.             select month(dtcreate) as m
  32.             from @t
  33.             union
  34.             select month(dtsubmit)
  35.             from @t
  36.         ) as m
  37.         left join (
  38.             select month(dtcreate) as m, count(*) as c
  39.             from @t
  40.             group by month(dtcreate)
  41.         ) as t1
  42.         on m.m = t1.m
  43.         inner join (
  44.             select month(dtcreate) as m, count(*) as c
  45.             from @t
  46.             group by month(dtcreate)
  47.         ) as t2
  48.         on m.m >= t2.m
  49.         group by m.m
  50.     ) as r1
  51.     full outer join (
  52.         select m.m, sum(t2.c) as runcount
  53.         from (
  54.             select month(dtcreate) as m
  55.             from @t
  56.             union
  57.             select month(dtsubmit)
  58.             from @t
  59.         ) as m
  60.         left join (
  61.             select month(dtsubmit) as m, count(*) as c
  62.             from @t
  63.             where dtsubmit is not null
  64.             group by month(dtsubmit)
  65.         ) as t1
  66.         on m.m = t1.m
  67.         inner join (
  68.             select month(dtsubmit) as m, count(*) as c
  69.             from @t
  70.             where dtsubmit is not null
  71.             group by month(dtsubmit)
  72.         ) as t2
  73.         on m.m >= t2.m
  74.         group by m.m
  75.     ) as r2
  76.     on r1.m = r2.m
  77. ) as x
  78. pivot (
  79.     sum(c)
  80.     for d in ([1], [2], [3], [4])
  81. ) as PivotTable;
Lines 1-11 is set up. Note that everything from line 24 to 76 is to accomodate the total row. If possible, I would drop that and handle it in reporting.
Dec 8 '11 #3

NeoPa
Expert Mod 15k+
P: 31,186
I expect Rabbit's got a better answer than I have for this question (that I assume is related to Jet SQL rather than T-SQL as his earlier answer indicates he probably thought it referred to), but I'll put in what I remember / understand.

I should start by saying that cross-tabs are a form of query I never use, but I can work some things out I expect.

Firstly, I believe that any query can either provide the details, OR the totals, but not both (without recourse to UNIONing the results of two queries, which is what I expect you'll need to do here).

I also understand there is a way to specify the full title list for cross-tabs which reflect sparse data, but which you want to show fully (Months of the year are a good example of this). The PIVOT clause can include an IN() qualifier which lists the values to be used.

The following gives a brief outline of what's available (You can find more from the TRANSFORM link, when you are Finding Jet SQL Help) :

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM aggfunction
  2.           selectstatement
  3. PIVOT     pivotfield [IN (value1[, value2[, ...]])]
Dec 9 '11 #4

100+
P: 759
Hello !

Smiley,
Is almost impossible for me to have an answer when YOU ask.
What I wish to say is that I NEVER design an SQL string but I use SQL in my VBA extensively (maybe more than really is need).

Every time, I designed the query in Query Design View then I switched to SQL View from where I copied the SQL string and I pasted it in VBA in order to use it as a "template".

I don't know if my "technique" can help you but I am sure that can be very helpful for others (who read this thread) not as skilled as you.
Dec 9 '11 #5

NeoPa
Expert Mod 15k+
P: 31,186
There's wisdom in that. Getting the queries designed by Access first and then using or studying the resultant SQL can help you learn more about what sort of things can be done and how. I learnt most of my SQL that way (and some of the cleverer stuff I actually learned from some of Rabbit's posts). It's not the last word though. It has limitations, so for going beyond what Access does for you there is the Help system available (See earlier post for link).
Dec 9 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.