New To SQL, hard problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

New To SQL, hard problem

Author

Topic

suppergenus
Starting Member

4 Posts

Posted - 2009-02-10 : 19:11:03

Hi All,

I'm new to sql but not to more traditional programming. I'm having a hard time wrapping my head around this problem and thought I'd see if anyone could offer me some insight.

The basic problem, I have information across a couple of tables that I need to retrieve and format in a certain way (I believe, my entire premise is open to suggestions

)

I can retrieve the data from the multiple tables and get it into this format:


ExpenseType	Code	Description	Amount
ACC	        BGO	   Bingo	1
ADV	        BGO        Bingo	1
DEP	        BGO	   Bingo	1
EQP	        BGO	   Bingo	1
OFC	        BGO	   Bingo	1
REN	        BGO	   Bingo	1
WAG	        BGO	   Bingo	1
ACC	        CAL    	   Calcutta	2
ADV	        CAL  	   Calcutta	2
OFC	        CAL	   Calcutta	2
ACC	        RFL	   Raffle	3
ADV	        RFL	   Raffle	3
OFC	        RFL	   Raffle	3

I want to get it into this format..



ExpenseType	Bingo	Calcutta	Raffle
ACC	           1     	2	3
ADV	           1	        2	3
DEP	           1      	0	0
EQP	           1	        0	0
OFC	           1	        2	3
REN	           1	        0 	0
WAG	           1	        0	0

I have been reading a lot about pivots and things like that but it is a lot to take in and my example is simplified. I appreciate any advice that can be given.

James

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-02-10 : 19:43:00

A few different ways to approach this... one way using your setup:


select  
      ExpenseType,
	sum(case when Code = 'BGO' then Amount else 0 end) as [Bingo],
	sum(case when Code = 'CAL' then Amount else 0 end) as [Calcutta],
	sum(case when Code = 'RFL' then Amount else 0 end) as [Raffle]
from	YourTable
group
by	ExpenseType

If you want, post the schema of the tables where the data exists before you "retrieve the data from the multiple tables." Perhaps we can eliminate some overhead by querying the source directly.

Nathan Skerl

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 20:35:12

[code]Select ExpenseType,Coalesce(Bingo,0)as Bingo,Coalesce(Calcutta,0)as Calcutta
from
(Select ExpenseType,Description,Amount from Table) M
PIVOT
(SUM(Amount) FOR Description in ([Bingo],[Calcutta]))
Z[/code]

suppergenus
Starting Member

4 Posts

Posted - 2009-02-10 : 20:51:22

Not sure exactly what you're asking for (<-- noob), there are two tables that the data is coming from here and I retrieve it (to give the listing the original post) using this statement:


SELECT	      OE.[ExpenseType]
	     ,GC.[description]
             ,OGC.[Code]
             ,OE.[Amount]
FROM [db1].[dbo].[OpGaCost] as OGC

INNER JOIN [db1].[dbo].[OpExpenses] as OE
ON OGC.CostID = OE.CostID

INNER JOIN [db2].[dbo].[GC] as GC
on OGC.Code = GC.game_cd

WHERE UserID = 666
ORDER BY Code ASC

OpGaCost -
UserID int <PK>
Code char(3) <PK>
CostID Int Identity <FK to OpExpenses>

OpExpenses -
CostID <PK> <FK to OpGaCost>
ExpenseType <PK>
Amount numeric(10, 2)

and GC is a look up table.. three letter code for the game in one column and description in the other column.

Is that what you were asking for?

Supper

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 20:55:02

Try with same logic we have provided. It will work for you.

suppergenus
Starting Member

4 Posts

Posted - 2009-02-11 : 11:46:23

quote:
Originally posted by sodeep

Try with same logic we have provided. It will work for you.

Back at work now so I'll post back when I make some progress.

Thanks for all the replies.

suppergenus
Starting Member

4 Posts

Posted - 2009-02-11 : 12:20:28

quote:
Originally posted by nathans

A few different ways to approach this... one way using your setup:

select
ExpenseType,
sum(case when Code = 'BGO' then Amount else 0 end) as [Bingo],
sum(case when Code = 'CAL' then Amount else 0 end) as [Calcutta],
sum(case when Code = 'RFL' then Amount else 0 end) as [Raffle]
from YourTable
group
by ExpenseType

If you want, post the schema of the tables where the data exists before you "retrieve the data from the multiple tables." Perhaps we can eliminate some overhead by querying the source directly.

Nathan Skerl

okay, big step in the right direction! Thanks for the help so far, I'm still working on it but I believe that was the big hurdle.

Thanks,

James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources