How to form this query - 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

How to form this query

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2007-01-14 : 02:19:56

I have a table SIM_Temp where I ahve three fields val1,val2,val3. I have some data like

SIM_Temp
--------

Val1----------Val2-------------Val3
===================================

AIRMECH------172371.13---------2004
AIRMECH------4269.5------------2003
ETA----------2871704.6724------2005
ETA----------6244149.0769------2004
ETA----------9046873.6504------2003

What I want is, I want this to be read like

CustomerName 2003------------2004----------2005

AIRMECH------------4269.5---------172371.13-----Nil
ETA--------------9046873.6504-----6244149.0769--9046873.6504

Please advice me, how could I do this.

Thank you
Ceema

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-14 : 02:35:34

[code]
select ComputerName = Val1,
max(case when Val3 = 2003 then Val2 end) as [2003],
max(case when Val3 = 2004 then Val2 end) as [2004],
max(case when Val3 = 2005 then Val2 end) as [2005]
from SIM_Temp
group by Val1
[/code]

ceema
Yak Posting Veteran

80 Posts

Posted - 2007-01-14 : 02:38:11

Hello khtan,

Thank you so much, it's working fine.

Thank you
Ceema

quote:

Originally posted by khtan


select ComputerName = Val1,
       max(case when Val3 = 2003 then Val2 end) as [2003],
       max(case when Val3 = 2004 then Val2 end) as [2004],
       max(case when Val3 = 2005 then Val2 end) as [2005]
from   SIM_Temp
group by Val1

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-01-15 : 12:58:41

khtan,

just curious why do you need use max in the query?

Necessity is the mother of all inventions!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-15 : 13:00:24

to get the max value.
if it's only one value you could've used min.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-15 : 13:23:29

Because when no value is found in the intersection, a NULL is inserted.
Any integer value is greater than a NULL value, therefore you go for MAX.
And as Mladen says, MIN is also of use.

Peter Larsson
Helsingborg, Sweden

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-01-15 : 14:24:33

Thank you , I was wondering if there was a way to use just the CASE without MAX or MIN. Is possible for the same query?

Necessity is the mother of all inventions!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-15 : 15:13:54

quote:
Originally posted by Vijaykumar_Patil

Thank you , I was wondering if there was a way to use just the CASE without MAX or MIN. Is possible for the same query?

Necessity is the mother of all inventions!

That depends on what you are trying to do.

It's something you could try for yourself to see what happens.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources