Alternate rows Logic - 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)

Alternate rows Logic

Author

Topic

linok29
Starting Member

4 Posts

Posted - 2008-12-05 : 01:10:49

hey Guys,

How can i set flag 1 and 0 for alternate rows using sql query?

your help is appreciated...

Id......Code....................Language........Description..........Flag
------------------------------------------------------------------------
1.......Heating Cables......... German......... Heizbänder/Heizkabel...1
............................... Russian........ ??????? ??????.........1
............................... English........ eating Cables..........1
2.......Heating Panels......... German......... Heizbänder/Heizkabel...0
............................... Russian........ ??????? ??????.........0
............................... English........ eating Cables..........0
3.......Trnsformers............ German......... Heizbänder/Heizkabel...1
............................... Russian........ ??????? ??????.........1
............................... English........ eating Cables..........1
4.......TT3000................. German......... Heizbänder/Heizkabel...0
............................... Russian........ ??????? ??????.........0
............................... English........ eating Cables..........0

i Hope this gives u clear idea about my requirement...

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-12-05 : 02:25:43

Update TABLE
SET Flag =
CASE WHEN Id%2 = 0 THEN 0
ELSE 1
END

Why does Russian and English row below Id 1 does not have values for Id and Code. Is it just that you did not show?

linok29
Starting Member

4 Posts

Posted - 2008-12-05 : 02:35:53

Hey CVraghu..
thanks for your help..

but number of sub rows may vary for ID so this wont work for me
i guess i need to check whether value of ID column when it is changed.

Thanks.

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-12-05 : 02:38:48

What do you mean by sub rows. What is the Id for the sub rows?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 02:46:25

will ID value be always continuos?

linok29
Starting Member

4 Posts

Posted - 2008-12-05 : 03:10:26

it is something like....

Id......Code....................Language........Description..........Flag
------------------------------------------------------------------------
1.......Heating Cables......... German......... Heizbänder/Heizkabel...1
1.............................. Russian........ ??????? ??????.........1
1.............................. English........ eating Cables..........1
1.............................. English........ eating Cables..........1
2.......Heating Panels......... German......... Heizbänder/Heizkabel...0
2.............................. Russian........ ??????? ??????.........0
2.............................. English........ eating Cables..........0
3.......Transformers........... German......... Heizbänder/Heizkabel...1
3.............................. Russian........ ??????? ??????.........1
3.............................. English........ eating Cables..........1
4.............................. Plish.......... eating Cables..........0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 03:28:40

[code]declare @Test table
(
Id int,
Code varchar(100),
Language varchar(100),
Description nvarchar(100),
Flag int
)

insert into @test (Id,Code,Language,Description)
select 1,'Heating Cables','German','Heizbänder/Heizkabel' union all
select 1,NULL, 'Russian','??????? ??????' union all
select 1,NULL,'English','eating Cables' union all
select 1,NULL, 'English','eating Cables' union all
select 2,'Heating Panels','German','Heizbänder/Heizkabel' union all
select 2,NULL, 'Russian','??????? ??????' union all
select 2,NULL, 'English','eating Cables' union all
select 3,'Transformers','German','Heizbänder/Heizkabel' union all
select 3,NULL, 'Russian','??????? ??????' union all
select 3,NULL, 'English', 'eating Cables' union all
select 4,NULL, 'Plish', 'eating Cables' union all
select 7,'Transformers','German','Heizbänder/Heizkabel' union all
select 7,NULL, 'Russian','??????? ??????' union all
select 7,NULL, 'English', 'eating Cables' union all
select 8,NULL, 'Plish', 'eating Cables'

Update t
set t.flag=rnk
from
(
select *,dense_rank() over (order by id) %2as rnk from @Test
)t

select * from @Test

output
-------------------------------------------------------------
Id Code Language Description Flag
1 Heating Cables German Heizbänder/Heizkabel 1
1 NULL Russian ??????? ?????? 1
1 NULL English eating Cables 1
1 NULL English eating Cables 1
2 Heating Panels German Heizbänder/Heizkabel 0
2 NULL Russian ??????? ?????? 0
2 NULL English eating Cables 0
3 Transformers German Heizbänder/Heizkabel 1
3 NULL Russian ??????? ?????? 1
3 NULL English eating Cables 1
4 NULL Plish eating Cables 0
7 Transformers German Heizbänder/Heizkabel 1
7 NULL Russian ??????? ?????? 1
7 NULL English eating Cables 1
8 NULL Plish eating Cables 0[/code]
this can even handle gaps in ID values

linok29
Starting Member

4 Posts

Posted - 2008-12-05 : 04:11:29

hey visakh16

Thanks a lot....
It worked for me.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:33:08

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources