Stuck on a statement - 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)

Stuck on a statement

Author

Topic

cardgunner

326 Posts

Posted - 2008-03-10 : 11:02:31

I have the following example


create table tmptbl02102008 ( unit Varchar(20), pos integer, seq integer, ppos integer, pseq integer)

insert into tmptbl02102008
	select '30257', 10,1,0,0 union all
	select '', 10,2,10,1 union all
	select '', 10,3,10,1 union all
	select '30258', 20,1,0,0 union all
	select '', 20,2,20,1 union all
	select '', 20,3,20,1

I would like to get

tmpunit
__________
30257 10 1
30257 10 2
30257 10 3
30258 20 1
30258 20 2
30258 20 3

I'm struggling with what I have to do. something like this

Case when unit = '' then unit where (pos=ppos) and (seq=pseq) else unit.

Any ideas how I can make that case statement work? I'm drawing a BIG blank.

Card Gunner

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-10 : 11:13:34

[code]
declare @tmptbl02102008 table (
unit Varchar(20),
pos integer,
seq integer,
ppos integer,
pseq integer
)

insert into @tmptbl02102008
select '30257', 10,1,0,0 union all
select '', 10,2,10,1 union all
select '', 10,3,10,1 union all
select '30258', 20,1,0,0 union all
select '', 20,2,20,1 union all
select '', 20,3,20,1

select * from (
select * from @tmptbl02102008 where unit <> ''
union all
select
a.unit,
b.pos,
b.seq,
b.ppos,
b.pseq
from @tmptbl02102008 a, @tmptbl02102008 b
where b.ppos=a.pos and b.pseq=a.seq
) b
order by 1,2,3,4
[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-10 : 11:27:49

[code]SELECT COALESCE(f.unit, r.unit) AS unit,
r.pos,
r.seq
FROM @@tmptbl02102008 AS r
INNER JOIN (
SELECT MAX(unit) AS unit,
pos
FROM @@tmptbl02102008
GROUP BY pos
) AS f ON f.pos = r.pos[/code]

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2008-03-10 : 11:47:08

Thanks jhocutt and Peso,

I tried jhocutt's example and it worked great.

It's good to see not everyone has the weekend funk clouding thier thoughts. It was a great help.

Card Gunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources