Prev: how SIN, COS and TAN works on sql server
Next: Thanks a lot
From: Marilyn on 24 May 2010 16:53
Hi,

How do I get the desired result for the following sample Table1 data:

Source Table: Table1
Columns: Mukey State L11 L12 L21 L22
m657753 AL 8.00 0.00 5.00 1.00
m657753 TN 0.00 1.00 3.00 0.00
m657754 AL 1.00 6.00 2.00 27.00
M657754 TN 0.00 4.00 1.00 5.00

Desired result:
Target Table: Table2
Columns: Mukey State LandUse
m657753 AL L11
m657753 TN L21
m657754 AL L22
m657754 TN L22

Thanks in advance.
From: Tom Cooper on 24 May 2010 17:23
You didn't specify what to do in the case of ties, and whether or not the
Lxx columns can be NULL. But the following is one possibility as long as
you don't have NULLs.

Insert Table2(Mukey, State, LandUse)
Select Mukey, State,
Case When L11>= L12 And L11>=L21 And L11>=L22 Then 'L11'
When L12>=L21 And L12>=L22 Then 'L12'
When L21>=L22 Then 'L21'
Else 'L22' End As LandUse
From Table1

You could also do it with an UNPIVOT and then using the ROW_NUMBER()
function on the UNPIVOTed result to find the largest value. But if you
don't have NULLs the above will probably be more efficient than an UNPIVOT
solution.

Tom

"Marilyn" <Marilyn(a)discussions.microsoft.com> wrote in message
news:AD5B208A-8FBA-4C0D-87E0-3B33BF7CA806(a)microsoft.com...
> Hi,
>
> How do I get the desired result for the following sample Table1 data:
>
> Source Table: Table1
> Columns: Mukey State L11 L12 L21 L22
> m657753 AL 8.00 0.00 5.00 1.00
> m657753 TN 0.00 1.00 3.00 0.00
> m657754 AL 1.00 6.00 2.00 27.00
> M657754 TN 0.00 4.00 1.00 5.00
>
> Desired result:
> Target Table: Table2
> Columns: Mukey State LandUse
> m657753 AL L11
> m657753 TN L21
> m657754 AL L22
> m657754 TN L22
>
> Thanks in advance.

From: --CELKO-- on 25 May 2010 01:29
It would really help if you would post DDL instead of your personal
narratives.
 | 
Pages: 1
Prev: how SIN, COS and TAN works on sql server
Next: Thanks a lot