Shoud I use self join? - 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 2000 Forums

Transact-SQL (2000)

Shoud I use self join?

Author

Topic

RaxSuur
Starting Member

7 Posts

Posted - 2005-08-23 : 07:52:56

Hallo
I'm wondering, is it posiible to make SQL query, that generates following.

Source table:

Step_nr--ColA--ColB
1--------req1--12
1--------req1--14
1--------req2--11
1--------req2--13
1--------req2--10
2--------req1--17
2--------req1--19
2--------req1--15
2--------req2--16
2--------req2--9

Generated table should be like:

Step_nr--Sum(req1)--Sum(req2)
1--------26---------34
2--------51---------25

Can enyone help me please?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-23 : 08:12:28

yes:


Select 
	Step_Nr, 
	Req1 = sum(case when colA='req1' then ColB else 0 end), 
	Req1 = sum(case when colA='req1' then ColB else 0 end)
From <yourTable>
Group By Step_Nr

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-23 : 08:33:31

and use req2 as column name for the second calculation

Select
Step_Nr,
Req1 = sum(case when colA='req1' then ColB else 0 end),
Req2 = sum(case when colA='req2' then ColB else 0 end)
From <yourTable>
Group By Step_Nr

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-23 : 08:39:10

hehehe ooops

Thanks for error checking me

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

RaxSuur
Starting Member

7 Posts

Posted - 2005-08-23 : 12:45:24

Thanks, but I forgot to say that I work in Access. I can't get work this code in Access

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-23 : 12:50:35

try:


Select 
	Step_Nr, 
	Req1 = iif(colA='req1',ColB,0), 
	Req2 = iif(colA='req2',ColB,0)
From <yourTable>
Group By Step_Nr

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

RaxSuur
Starting Member

7 Posts

Posted - 2005-08-23 : 13:09:37

Thank you both very much.
It is working fine. Finally I made this way:

Select
Step_Nr,
SUM(iif(colA='req1',ColB,0)) AS Req1,
SUM(iif(colA='req2',ColB,0)) AS Req2
From <yourTable>
Group By Step_Nr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources