Query Help - 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 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

viewtifulljoe44
Starting Member

1 Post

Posted - 2008-08-01 : 01:47:42

I'm pretty new to SQL so don't laugh if this is an easy question. :)

I have a table with this structure:

Node | 1 | 2 | 3 | 4 | 5
====================
100 | 5 | 3 | 3 | 0 | 0
101 | 6 | 3 | 0 | 0 | 0
102 | 4 | 8 | 7 | 9 | 2
103 | 2 | 0 | 5 | 0 | 0
104 | 3 | 0 | 0 | 0 | 0

What this represents is the results of a sampling of each "node." So say that of the 11 times node 100 was sampled it was equal to "1" five times, equal "2" three times, and equal to "3" three times. None of its samples were equal to four or five. The output I'm looking for is the level reached by each "node." So an output like this for the above data:

Node | Max Level Reached
====================
100 | 3
101 | 2
102 | 5
103 | 3
104 | 1

I have tried the following which did not work:

Select Node,
Case When "5" > 0 Then "5"
when "4" > 0 Then "4"
when "3" > 0 Then "3"
when "2" > 0 Then "2"
else "1"
end as MaxLev
into #newtable
Select * from #newtable

Any help would be much appreciated.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-01 : 03:04:19

I hope this helps

Declare @Table table
( Node varchar(20),
L1 int,
L2 int,
L3 int,
L4 int,
L5 int)
Insert into @Table
Select '101',1,2,3,4,5 union all
Select '102',1,2,3,4,0 union all
Select '103',1,2,3,0,0 union all
Select '104',1,2,0,0,0 union all
Select '105',1,0,0,0,0

Select Node ,
Case
when L5 >0 then '5'
when L4 >0 then '4'
when L3 >0 then '3'
when L2 >0 then '2'
when L1 >0 then '1'
else '0'
End AS LevelR
from @Table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:06:49

or try using UNPIVOT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources