Very hard question on how to query data and return by months

Ljhopkins

I have a data set that returns the following

Customer	pncount	pn%	monthval
ABC	5	30	01/01/2007
DEF	9	20	01/01/2007
GHI	4	40	01/01/2007
ABC
DEF	4	90	02/01/2007
GHI	7	10	02/01/2007
ABC	4	5	03/01/2007
DEF
GHI	6	100	03/01/2007

but I need this

	01/01/2007	01/01/2007	02/01/2007	02/01/2007	03/01/2007	03/01/2007
abc	5	30			4	5
def	9	20	4	90
ghi	4	40	7	10	6	100

Any Idea Help

hunchback

- Which version of SQL Server are you using

- Can you post the query

Search for PIVOT in this group.

AMB

Ljhopkins

sql 2003

NorthwindDBA

This is really a very hard question. I doubt anyone in the group can give you a direct solution for this using SQL 2003.

DaleJ

That's not so hard...just run 2/5's of it on SS2000 and 3/5's of it on SS2005!

Ljhopkins

say what

NorthwindDBA

DaleJ wrote:

That's not so hard...just run 2/5's of it on SS2000 and 3/5's of it on SS2005!

You got him confused. It should be the other way around... run 3/5's of it on SS2000 and 2/5's of it on SS2005!

hunchback

Hi DaleJ,

LOL!

AMB

P.S. Sorry for posting this msg, but I couldn't resist

NorthwindDBA

Hi Ljhopkins, we're just playin'.

Do hunchback's suggestion and search for PIVOT. You are using SQL 2005 right

hunchback wrote:

- Which version of SQL Server are you using - Can you post the query Search for PIVOT in this group. AMB

David Frommer

Some of them are having a little joke with your confusion ;-).

There is no version 2003 for SQL Server. You will have either 6.5, 7.0, 2000, or 2005. We need to know which one you are using to determine the proper answer.

Ljhopkins

ok guys I ment 2000

good laugh

at the poor squibs expence

LOL

asiaindian

Hello,

You seem to be looking for a crosstab query. In SQL 2005, using PIVOT function would have helped. But in SQL 2000, PIVOT is achived by writing a query using CASE and SUM functions. A good example is located at http://dotnetjunkies.com/WebLog/thomasswilliams/archive/2005/10/23/133383.aspx.

I hope this helps....

Regards...

asiaindian

Hello,

Check this out too...

http://www.mssqltips.com/tip.asp tip=937

Regards...

hunchback

Ljhopkins,

Here is another article about pivoting rows in SS 2000.

How to rotate a table in SQL Server

http://support.microsoft.com/default.aspx scid=kb;en-us;175574&Product=sql

AMB