Re: reformat the output

From: Delbert Glass (delbert_at_noincoming.com)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 15:35:19 -0600

create table mytable(
  mycolumn int not null
)

insert into mytable values (2004)
insert into mytable values (2005)
insert into mytable values (2003)
insert into mytable values (2008)
insert into mytable values (2006)
insert into mytable values (2007)

--delete from mytable where mycolumn = 2008
--delete from mytable where mycolumn = 2004

select mycolumn as year
from mytable
order by year

select
  cast(mycolumn as varchar(4))
  +
  case
    when exists (
      select *
      from mytable ii
      where ii.mycolumn = oo.mycolumn + 1
    ) then '/' + cast(mycolumn + 1 as varchar(4))
    else ''
  end
  as year
from mytable oo
where mycolumn % 2 = 1
order by year

Bye,
Delbert Glass

"joe" <pearl_77@hotmail.com> wrote in message
news:%234LBYQsBEHA.3852@TK2MSFTNGP10.phx.gbl...
> Sorry My bad,
> I made a little mistake,
>
> Original output:
>
> year
> ------
> 2004
> 2005
> 2003
> 2008
> 2006
> 2007
>
>
> I really want to convert to following format:
>
> Desired Output:
>
> year
> --------
> 2003/2004
> 2005/2006
> 2007/2008
>
>
> Note: if there is no 2008 year,
> then output will be
>
> year
> --------
> 2003/2004
> 2005/2006
> 2007
>
>
> "joe" <pearl_77@hotmail.com> wrote in message
> news:uDEJAEsBEHA.3184@TK2MSFTNGP09.phx.gbl...
> > Orginal output
> >
> > year
> > ------
> > 2004
> > 2005
> > 2003
> > 2008
> > 2006
> > 2007
> >
> >
> > how do I convert to such an output :
> >
> > c1 c2 c3 c4
> > ---------- --------- -------------- ------------
> > 2003/2004 2004/2005 2006/2007 2007/2008
> >
> >
> > Note: let's say we don't have 2008 year,
> >
> > then c4 will be 2007 , not 2007/2008
> >
> >
> >
>
>