Data manipulation

• Kabir Patel

  Grasshopper

  Points: 14

  More actions

  November 14, 2006 at 1:14 pm

  #193163

  I have data in a table in the following format:

  FactoryID               FactoryJob

  -------------------------------------

  Factory1                No1

  Factory1                No2

  Factory2                No1

  Factory3                No3

  Factory3                No4

  Factory3                No5

  and I would like the data out in the format of:

  FactoryID      FactoryJob1     FactoryJob2      FactoryJob3

  --------------------------------------------------------------

  Factory1        No1              No2             NULL

  Factory2        No1              NULL            NULL

  Factory3        No3              No4             No5

  Is there an easy way to do this withouth using a cursor?

  Cheers,

  Kabir

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  November 14, 2006 at 3:26 pm

  #671796

  you might not get the same result but try this.

  Select  Factories.FactoryID,F1.FactoryJob,F2.FactoryJob,

   F3.FactoryJob,F4.FactoryJob,F5.FactoryJob

  from ( 

   select distinct ltrim(rtrim(FactoryID)) as FactoryID from Factory) Factories

  left outer join (Select * from Factory where ltrim(rtrim(FactoryJob)) ='No1') F1 on ltrim(rtrim(Factories.FactoryID)) = ltrim(rtrim(F1.FactoryID))

  left outer join (Select * from Factory where ltrim(rtrim(FactoryJob)) ='No2') F2 on ltrim(rtrim(Factories.FactoryID)) = ltrim(rtrim(F2.FactoryID))

  left outer join (Select * from Factory where ltrim(rtrim(FactoryJob)) ='No3') F3 on ltrim(rtrim(Factories.FactoryID)) = ltrim(rtrim(F3.FactoryID))

  left outer join (Select * from Factory where ltrim(rtrim(FactoryJob)) ='No4') F4 on ltrim(rtrim(Factories.FactoryID)) = ltrim(rtrim(F4.FactoryID))

  left outer join (Select * from Factory where ltrim(rtrim(FactoryJob)) ='No5') F5 on ltrim(rtrim(Factories.FactoryID)) = ltrim(rtrim(F5.FactoryID))

• BeerBeerBeer

  SSCarpal Tunnel

  Points: 4021

  More actions

  November 14, 2006 at 5:14 pm

  #671814

  The following link gives you a simplified version of how to do this

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=319791

  And there were some other suggestions (including the above link) here

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=319189#bm319729

   

   

  ---

  Regards,

  Steve

  Life without beer is no life at all

  All beer is good, some beers are just better than others