Cols comparison in sql2000 - 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 2000 Forums

SQL Server Development (2000)

Cols comparison in sql2000

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-06-05 : 23:48:25

Please help me in getting the output..

Table:

Tid Test1 Test2 Test3
----------- ------ ------ ------
1 45 45 90
2 60 60 60
3 15 20 25
4 80 80 80
5 35 45 35
6 36 38 40

Rules:
I want to display the rows with a category based on certain conditions..
If in a row two columns have same values then i make category1.
If in a row three columns have same values then it should be category2.
If in a row three columns have different values then it should be Category3.

Expected output:

I want the ouput:

Tid Test1 Test2 Test3 Desc
----------- ------ ------ ------ -----
1 45 45 90 Category1
2 60 60 60 Category2
3 15 20 25 Category3
4 80 80 80 Category2
5 35 45 35 Category1
6 36 38 40 Category3

Thanks for your help in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 00:37:43

use a case when to do it


select Tid, Test1, Test2, Test3,
       desc = case when Test1 = Test2 or Test1 = Test3 or Test2 = Test3 then 'Categoy1'
                   when Test1 = Test2 and Test2 = Test3 then 'Category2'
                   when Test1 <> Test2 and Test2 <> Test3 then 'Category3'
                   end
from   yourtable

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-06 : 01:09:39

Select Test1 , Test2 , Test3 , Case when (Test1 = Test2 and Test2 = Test3 ) Then 'Category2'
when ((Test1 = Test2 or Test1 = Test3) and Test2 <> Test3 ) Then 'Category1'
When (Test1 <> Test2 and Test2 <> Test3 and Test1 <> Test3 )Then 'Category3' End as 'CateGory'
From @Temp

ranganath
Posting Yak Master

209 Posts

Posted - 2007-06-06 : 01:48:29

Declare @Temp Table (Tid int, Test1 int, Test2 int, Test3 Int)
Insert into @Temp

Select 1,45,45,90 union all
select 2,60,60,60 union all
select 3,15,20,25 union all
select 4,80,80,80 union all
Select 5,35,45,35 union all
Select 6,36,38,40

--Select * From @Temp

Select Test1 , Test2 , Test3 , Case when (Test1 = Test2 and Test2 = Test3 ) Then 'Category2'
when ((Test1 = Test2 or Test1 = Test3) and Test2 <> Test3 ) Then 'Category1'
When (Test1 <> Test2 and Test2 <> Test3 and Test1 <> Test3 )Then 'Category3' End as 'CateGory'
From @Temp

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-06-06 : 07:52:39

Thanks for your reply..
One more condition i need to apply sum (test1,test2,test3) should not be greater than 180.If it greater then 180 then it should be under Category 4..

Please help.......

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 08:11:54

just add another like of when test1 + test + ... to it.

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-06-06 : 08:34:23

like......

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 08:35:36

like this


when test1 + test2 + test3 < 180 then 'Category4'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-06 : 10:12:46

quote:
Originally posted by khtan

like this

when test1 + test2 + test3 < 180 then 'Category4'

KH

You should handle NULL as well

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-06 : 10:17:48

Not sure if this works


Select Tid,count(Test1) as counting from 
(
    Select Tid, Test1 from table
    union all
    Select Tid, Test2 from table
    union all
    Select Tid, Test3 from table
) T
group by Tid

Now based on counting you can categorize

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-06 : 11:08:26

Be sure to read up on normalization; this is a very bad database design, if you can change it.

see:

http://www.datamodel.org/NormalizationRules.html

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources