create a view with qty summed by Field description

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

General SQL Server Forums

New to SQL Server Programming

create a view with qty summed by Field description

Author

Topic

lollie
Starting Member

1 Post

Posted - 2011-07-08 : 07:29:39

Have the following table

IFAC,IPROD, USE_PER, QTY
aa ,aa345 , q1 , 100
ad , ad234, q1 , 250
ad , ad234, q3 , 120
ba , ba124, q1 , 150

want to create a view as follow
IFAC, IPROD, Q1, Q2, Q3, Q4
aa , aa345 , 100, 0, 0, 0
ad , ad234 , 250, 0, 120, 0
ba , ba124 , 150, 0, 0, 0

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-08 : 07:57:27

create view <viewname>
as
select
IFAC,
IPROD,
isnull(sum(case when USE_PER = 'q1' then QTY end),0) as QTY,
isnull(sum(case when USE_PER = 'q2' then QTY end),0) as QTY,
isnull(sum(case when USE_PER = 'q3' then QTY end),0) as QTY,
isnull(sum(case when USE_PER = 'q4' then QTY end),0) as QTY
from <YOURTABLE>
group by IFAC,IPROD
go

--Ranjit

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-08 : 07:58:50

quote:
Originally posted by Ranjit.ileni

create view <viewname>
as
select
IFAC,
IPROD,
isnull(sum(case when USE_PER = 'q1' then QTY end),0) as q1,
isnull(sum(case when USE_PER = 'q2' then QTY end),0) as q2,
isnull(sum(case when USE_PER = 'q3' then QTY end),0) as q3,
isnull(sum(case when USE_PER = 'q4' then QTY end),0) as q4
from <YOURTABLE>
group by IFAC,IPROD
go

--Ranjit

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources