divide column

• surma.sql

  SSCommitted

  Points: 1552

  More actions

  May 2, 2012 at 6:01 pm

  #260249

  hello friends

  i have task

  teacheridtlnametfnameschoolidschoolname

  2 john smith 152 carnegi

  2 john smith 155 monroville

  1 ricky johnston153 elementery

  3 ricky johnston157 alleghany

  1 steve ulman 151 banksville

  1 steve ulman 154 school

  1 steve ulman 156 pittsburgh

  i get this result by this query

  select t.teacherid,t.tlname,t.tfname, t.schoolid, s.schoolname from teacher t join school s on

  t.schoolid = s.schoolid order by t.tlname,t.tfname

  and i need result display like this

  teacherid tlnametfnameschoolidsecondary_schoolID schoolname

  2 john smith 152 carnegi

  2 john smith 155 monroville

  1 ricky johnston153 elementery

  3 ricky johnston 157 alleghany

  1 steve ulman 151 banksville

  1 steve ulman 154 school

  1 steve ulman 156 pittsburgh

  here is a data

  create table teacher (teacherid int, tlname varchar(12), tfname varchar(12), schoolid int)

  insert into teacher values (1, 'steve','ulman',151)

  insert into teacher values (2, 'john','smith',152)

  insert into teacher values (1, 'ricky','johnston',153)

  insert into teacher values (1, 'steve','ulman',154)

  insert into teacher values (2, 'john','smith',155)

  insert into teacher values (1, 'steve','ulman',156)

  insert into teacher values (3, 'ricky','johnston',157)

  select * from teacher

  create table school (schoolid int, schoolname varchar(12))

  insert into school values (151, 'banksville')

  insert into school values (152, 'carnegi')

  insert into school values (153, 'elementery')

  insert into school values (154, 'school')

  insert into school values (155, 'monroville')

  insert into school values (156, 'pittsburgh')

  insert into school values (157, 'alleghany')

  so please help me ASAP

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  May 2, 2012 at 6:25 pm

  #1482767

  try this

  ; with cte as

  (

  select t.teacherid,t.tlname,t.tfname, cast(t.schoolid as varchar) as schoolid, s.schoolname

  , rn = row_number() over (partition by t.tlname,t.tfname order by s.schoolid)

  from teacher t

  join school s on

  t.schoolid = s.schoolid

  )

  select c.teacherid , c.tfname , c.tlname

  ,schoolid = case when rn = 1 then c.schoolid else '' end

  ,secondary_schoolID = case when rn <> 1 then c.schoolid else '' end

  ,c.schoolname

  from cte c