new qurey results - 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

General SQL Server Forums

New to SQL Server Programming

new qurey results

Author

Topic

nemo74
Starting Member

1 Post

Posted - 2014-08-05 : 11:19:19

Hi Everyone,
I need some help, I'm new to SQL and learning fast, but coming up short. There are some formatting issues with my result example but I hope it can be understood.

This query:
SELECT [CustomerNumber]
,[Rev1]
,[Rev2]
,[Rev3]
,[ProductArea]
,[monthseq]
,[yearseq]

FROM [Data1].[dbo].[AllRev]

Where CustomerNumber ='7474' and monthseq = '1' and YearSeq = '2014'

Returns:
CustomerNumber Rev1 Rev2 Rev3
7474 66 202 0.00
7474 46 NULL 0.00

I need to change the query so that result changes to:
CustomerNumber Rev1 Rev2 Rev3 Rev1A Rev2A Rev3A
7474 66 202 0.00 46 Null 0.00

Please tell me if this is possable, its kicking my butt, I cannot figure it out

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-05 : 14:42:19

I know this doesn't fully cover your question, but you can do something like:

select customernumber
      ,yearseq
      ,monthseq
      ,sum(case when productarea=1 then rev1 else 0 end) as area1rev1
      ,sum(case when productarea=1 then rev2 else 0 end) as area1rev2
      ,sum(case when productarea=1 then rev3 else 0 end) as area1rev3
      ,sum(case when productarea=2 then rev1 else 0 end) as area2rev1
      ,sum(case when productarea=2 then rev2 else 0 end) as area2rev2
      ,sum(case when productarea=2 then rev3 else 0 end) as area2rev3
  from data1.dbo.allrev
 where customernumber='7474'
   and monthseq='1'
   and yearseq='2014'
 group by customernumber
         ,yearseq
         ,monthseq

This assumes your procuctarea is nummeric and you are interested in productarea 1 and 2. This can easily be changed to fit your situation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources