Query Help Needed... - 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

SQL Server Development (2000)

Query Help Needed...

Author

Topic

augustin_p
Starting Member

21 Posts

Posted - 2002-05-06 : 04:46:26

I have a table structure as below

Table Name: MaterialMaster

Materialcode MaterialName
-------------- ----------------
201 x
203 y
301 a
394 b
403 c

Now, I need a query to retrieve number of materials for each category. Each category shall start with a unique number. For e.g in the above records, let us assume that the material codes starting with '2' are 'Raw Materials' and material codes starting with '3' are 'Capital Goods' and material codes starting with '4' are 'Consumables'. i need a report as below:

Raw Materials Capital Goods Consumables
---------------- ---------------- ---------------
2 2 1

I need the best way to achieve this. The query should be best optimized for performance

Thanks,
prasanna

dsdeming

479 Posts

Posted - 2002-05-06 : 07:57:20

Something like this should do the trick (although you may have to pivot it ):

set nocount on

create table MaterialMaster
(
Materialcode varchar( 10 ), MaterialName varchar( 10 )
)

insert into MaterialMaster select '201', 'x'
insert into MaterialMaster select '203', 'y'
insert into MaterialMaster select '301', 'a'
insert into MaterialMaster select '394', 'b'
insert into MaterialMaster select '403', 'c'

select category = case left( Materialcode, 1 )
when '2' then 'Raw Materials'
when '3' then 'Capital Goods'
when '4' then 'Consumables'
else 'Unknown' end,
categorycount = count(*) from MaterialMaster group by left( Materialcode, 1 )

drop table MaterialMaster

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources