Need help w sql statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Need help w sql statement

Author

Topic

xrbbaker
Starting Member

2 Posts

Posted - 2007-06-06 : 10:20:44

I have a nasty huge query that I'm trying to work with. Rather than bring its ugliness in here I'm presenting a simplified example. I hope that if someone can point me in the right direction with this, I can translate the concept to the real sql. thanks very much for any help...

Suppose I have a sql statement like this:
select agent, houses_sold, month from table order by month;

would return:

Smith 4 Jan
Smith 6 Feb
Smith 8 Mar

How could I create a sql statement to have it return like this?

Smith 4 6 8

My need is that I'm going to be bringing back a lot of data into Excel and then doing Lookups on it, so I want to bring it back in a table-like format.

Russell Baker
VP, Path Technologies, Inc.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 10:23:20

It would be more simple and fast if you will do the transformation in the EXCEL itself. Excel provides this facility in Paste Special, Transpose option.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-06 : 10:27:44

EXCEL supports PIVOT

Madhivanan

Failing to plan is Planning to fail

xrbbaker
Starting Member

2 Posts

Posted - 2007-06-06 : 11:27:28

Gentlemen,

I use pivot tables quite a bit. The issue here is that I need to create a table such that I can then do VLOOKUPS against that table. The business problem is that I have a spreadsheet that has Project, Person, Forecasted hrs by month. After time goes by I want to programatically compare forecased hrs against actuals. I have to pull the Actual hrs from another system. I don't want the data to be returned like this:

Russ projecta Jan 150
Russ projectb Jan 40
Russ projecta Feb 200

I want the data to be returned like this:

Russ projecta 150 200
Russ projectb 40

Hence my simple example trying to figure out how to "columnarize" the answer set.

thx

Russell Baker
VP, Path Technologies, Inc.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 11:33:27

You will need something along these lines:

Select agent,
       case when month = 'Jan' then houses_sold else 0 end as Jan,
       case when month = 'Feb' then houses_sold else 0 end as Feb,
       case when month = 'Mar' then houses_sold else 0 end as Mar,
...
From Table

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources