Query Help - 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)

Query Help

Author

Topic

TheSpongebob
Starting Member

8 Posts

Posted - 2006-02-03 : 22:11:45

Hi.

Someone told me this could be done but I cannot figure out how to do it.

If I have the following Client_Sales table :

Client Prov AMOUNT
------ ---- ------
001 ON 100.00
001 AB 200.00
001 PQ 300.00
002 ON 150.00
002 AB 250.00
002 PQ 350.00

Is there any way I can write a query that will return
a single row for each Client and it's 3 Amounts.

Can it be done without sub-selecting ?

Example Returns :

Client AMT AMT AMT
------ ------ ------ ------
001 100 200 300
002 150 250 350

Thanx, Randy

TheSpongebob
Starting Member

8 Posts

Posted - 2006-02-03 : 22:48:06

I tried this and it works. I found the example in another post. Now, I have to ask myself ( and you all ), what kind of performance hit will I take by doing this over a plain old cursor?

select Client,
max(case when prov='ab' then Amount else Null END) as AlbertaAmt,
max(case when prov='on' then Amount else Null END) as OntarioAmt,
max(case when prov='pq' then Amount else Null END) as QuebecAmt,
max(case when prov='nw' then Amount else Null END) as NorthWestAmt
from test
group by Client

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-03 : 22:51:33

select t1.client ,
(select top 1 amount from tbl t2 where t2.client = t1.client order by prov) ,
select top 1 amount from (select top 2 prov, amount from tbl t2 where t2.client = t1.client order by prov) t3 order by prov desc),
select top 1 amount from (select top 3 prov, amount from tbl t2 where t2.client = t1.client order by prov) t3 order by prov desc)
from (select distinct client from tbl) t1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TheSpongebob
Starting Member

8 Posts

Posted - 2006-02-03 : 23:05:46

Thanx NR. I ran both those solutions through the analyser. They are both cool, except they both add in a sort ( small one allbeit ) Let's see what that turns into after 5 million iterations ;)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-03 : 23:28:36

[code]select Client,
sum(case when Prov = 'ON' then Amount else 0 end) as OntarioAmt,
sum(case when Prov = 'AB' then Amount else 0 end) as AlbertaAmt,
sum(case when Prov = 'PQ' then Amount else 0 end) as QuebecAmt
from test
group by Client[/code]

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources