July 18, 2002 at 9:05 am
Does anyone know a neat way to suppress repeating fields in a select statement? I.e.
If a table contains the following: -
c1c2
A1
A2
A 3
B4
B 5
I want a query to return: -
A1
<Null>2
<Null>3
B4
<Null>5
I could use a cursor and a temp table but am looking for a set based method.
Regards,
Andy Jones
.
July 18, 2002 at 10:30 am
This really sounds more like a presentation operation. What about having your front-end do this?
-Mike
Michael Levy
Michael Levy
ma_levy@hotmail.com
July 19, 2002 at 8:24 am
The last time I had to do this, I resorted to a cursor and temp table. I was thinking about it though, and if you can put the min value for c2 for each c1 in another table, you should be able to use a case to determine whether or not to display <null> for c1.
select c1 , min(c2) as 'c2'
into #min_c2
from my_data
group by c1
select case when a.c2 = b.c2 then a.c1 else null end as 'c1' , c2
from my_data a
inner join #min_c2 b on a.c1 = b.c1
order by c1 , c2
I haven't tested it, but that might do it.
Matthew Galbraith
July 22, 2002 at 12:12 pm
select case
when c2 =
(select min(c2)
from mytable
where c1 = o.c1
group by c1) then c1
else null
end,
c2
from mytable o
This seems to work for what you have described. Try it and let us know.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply