September 15, 2011 at 9:36 pm
Hi,
I have a large transaction table (millions of rows), that I need to report on for completeness.
Each date is expected to have a minimum number of transactions, I need to report on 1.5 years of data.
Right now, I have sql that looks like this:
select
COUNT(*),
CONVERT(VARCHAR(10), TransactionDate, 111)
from
Transactions
where
Location = 'Canada'
group by
CONVERT(VARCHAR(10), TransactionDate, 111)
order by
CONVERT(VARCHAR(10), TransactionDate, 111) asc
As you can see, I have a Where Location = clause in there, which gives results like this:
Transactions Date
------------ ----------
66 2011/01/01
231 2011/01/02
Following this approach, I could get data for all locations by leaving out the where clause, but the dates would repeat in the results, once for each location, which would look something like this:
Location Transactions Date
-------- ------------ ----------
Canada 66 2011/01/01
Canada 231 2011/01/02
America 481 2011/01/01
America 48 2011/01/02
What I'd like to get are results that look like this:
Date Canada America Location N Location N+200
----------- -------- ---------- ------------ ---------------
2011/01/01 66 481 1 2
2011/01/02 231 48 3 4
I'd tried a few things like generating the dates and looping over them, but I can't figure out how to expand my results horizontally with additional location results on a per date basis.
So far my server has been sufficiently fast to handle processing this data without issue.
I currently have 100 locations loaded with plans to load data for 100 additional locations that I'd like to generate this large data grid on, from there I'd like to apply where clauses on it, such as where count(*) < 100 and only return the dates and locations that have less then 100 transactions.
September 15, 2011 at 9:55 pm
You should use PIVOT statement.
September 15, 2011 at 10:14 pm
The Pivot should help you with this.
Here is an article with examples:
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply