Pivot Querrie using Dates

Question

Pivot Querrie using Dates

Marv_SQL

SSCommitted

Points: 1612
More actions
March 25, 2010 at 10:20 am

#218496

I am trying to write a query so that my results are in a crosstab format.
I want the sum of sales by employee grouped by the months going across.
basically I need
January Feb March
John 100 300 500
Dave 150 200 200
The table that I am using has a salesdate for each record row. I just want to sum up totals by months. Is that even possible with SQL 2005?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Marv-1058651 (3/25/2010)
I am trying to write a query so that my results are in a crosstab format.
I want the sum of sales by employee grouped by the months going across.
basically I need
January Feb March
John 100 300 500
Dave 150 200 200
The table that I am using has a salesdate for each record row. I just want to sum up totals by months. Is that even possible with SQL 2005?

Yes it's possible. You could either use PIVOT (see BOL for details), the CrossTab or the DynamicCrossTab method (see the related links in my signature for details).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Marv_SQL SSCommitted Points: 1612 More actions · Answer 2

Sweet this looks good. This is the code that I used:

select * from

(select DATEPART(M,DATETRANS) AS ORDERMONTH, DELTA,staffkey

FROM BIL_ARTRANSLOG

WHERE DATETRANS BETWEEN '1/1/2009' AND '10/1/2009'AND TRANSTYPELU= 3) SRC

PIVOT (SUM(DELTA) FOR ORDERMONTH IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS PVT

GO

Can the months be fields as well if I dump my results in a temp table?

Thanks a million

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Can the months be fields as well if I dump my results in a temp table?

Not quite sure what you mean by that. Can you draw us a picture?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Marv_SQL SSCommitted Points: 1612 More actions · Answer 4

My apologies for not being clear.

Can I extract the Months from the datepart on the pivot table and insert the month as a seperate field in a table?

January varchar(35)

February varchar(35)

March varchar(35)

K Cline Ten Centuries Points: 1391 More actions · Answer 5

Sounds like what you want is the month names as the column headers on your resultset.

Why not alias the column names instead of retrieving the results with "Select *"? I think most people will advise you to avoid using Select *.

Also, you may want to add some code to handle the Nulls... unless you aren't concerned with Nulls in your results.

Select StaffKey, IsNull([1], 0) As [January], IsNull([2], 0) As [February], IsNull([3], 0) As [March], etc.