Need Help Building a Simple Query (Newbie)

[caper11752]

Hi all,

I have a table called tblCodes that looks something like this:

Date Code Acct#
1/1/2010 WP41 985744
1/1/2010 WI03 754841
1/1/2010 WP41 545412
1/1/2010 WP41 747477
1/1/2010 WI03 212788
1/2/2010 WI03 124254
1/2/2010 WI03 714725
1/2/2010 WP41 996525

I need to build a query that will return results that will look something like this:

Date WP41_Count WI03_Count
01/01/2010 3 2
01/02/2010 1 2

I am VERY new to SQL and would appreciate the help. Basically, I need to produce a report that will give a daily count of each "Code".

Thanks in advance.

 

[bborissov]

That is not so easy as you think
You easy can get a result like this;
[tt]
Date Code Count
01/01/2010 WP41 3
01/01/2010 WI03 2
01/02/2010 WP41 1
01/02/2010 WI03 2
[/tt]
But converting it to PIVOT is not so easy even with PIVOT command introduced in SQL Server 2005, just because you SHOULD know beforehand WHICH coeds will be involved in the result of the query.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

If you want each code, then your need to use Dynamic Pivot.

See these threads in T-SQL MSDN forum

http://social.msdn.microsoft.com/Fo.../thread/697721b7-0577-4d3f-8b07-15ccf86f01a1/

and search this forum for 'Dynamic Pivot' or simply google on 'Dynamic Pivot'


PluralSight Learning Library