Combining Common Rows RSS

• • Reply
  

  jay.emery

  0 Points

  1 Post

  Combining Common Rows

  Mar 20, 2012 09:10 PM|LINK

  Trying to modifiy the statement below to combine similar rows. Any help is greatly appreciated. NTRANPOLE is not required in the final results, Just added it for information only.

  Data resides in 2 seperate tables.

  SELECT tidba.tranloc_ne.ntranloc,
         tidba.tranloc_ne.ccircid,
         tidba.tranloc_ne.cstructure,
         tidba.tranpole_ne.ntranpole,
         tidba.tranpole_ne.cheight,
         tidba.tranpole_ne.cclass,
         tidba.tranpole_ne.nposition,
         tidba.tranloc_ne.noldinsp
  FROM tidba.tranloc_ne
  INNER JOIN tidba.tranpole_ne
  ON tidba.tranloc_ne.ntranloc=tidba.tranpole_ne.ntranloc
  WHERE tidba.tranloc_ne.noldinsp = '0'
      AND tidba.tranloc_ne.ccircid IN ('301','349')
  ORDER BY tidba.tranloc_ne.ccircid, tidba.tranloc_ne.cstructure, tidba.tranpole_ne.nposition

  NTRANLOC	CCIRCID	CSTRUCTURE	NTRANPOLE	CHEIGHT	CCLASS	NPOSITION	NOLDINSP
  2347060	301	1	2292727	80	2	1	0
  2347060	301	1	2292728	80	2	2	0
  2347060	301	1	2292729	80	2	3	0
  2347059	301	2	2292725	100	1	1	0
  2347059	301	2	2292726	100	1	2	0

  Trying to group the data (using the first three columns) to look like the table below. The (1), (2) and (3) would come from the NPOSITION field above.

  NTRANLOC	CCIRCID	CSTRUCTURE	CHEIGHT(1)	CCLASS(1)	CHEIGHT(2)	CCLASS(2)	CHEIGHT(3)	CCLASS(3)	NOLDINSP
  2347060	301	1	80	2	80	2	80	2	0
  2347059	301	2	100	1	100	1			0

• • Reply
  

  gimimex

  Participant

  1052 Points

  157 Posts

  Re: Combining Common Rows

  Mar 20, 2012 11:59 PM|LINK

  Try something like that:

   

  SELECT tidba.tranloc_ne.ntranloc,
         tidba.tranloc_ne.ccircid,
         tidba.tranloc_ne.cstructure,
         e1.cheight as CHEIGHT_1,
         e1.cclass as CCLASS_1,
         e2.cheight as CHEIGHT_2,
         e2.cclass as CCLASS_2,
         e3.cheight as CHEIGHT_3,
         e3.cclass as CCLASS_3,
         e1.noldinsp
  FROM tidba.tranloc_ne
  INNER JOIN tidba.tranpole_ne as e1
      ON (tidba.tranloc_ne.ntranloc = e1.ntranloc) and (e1.nposition = 1)
  LEFT JOIN tidba.tranpole_ne as e2
      ON (tidba.tranloc_ne.ntranloc = e2.ntranloc) and (e2.nposition = 2)
  LEFT JOIN tidba.tranpole_ne as e3
      ON (tidba.tranloc_ne.ntranloc = e3.ntranloc) and (e3.nposition = 3)
  WHERE (tidba.tranloc_ne.noldinsp = '0') AND
        (tidba.tranloc_ne.ccircid IN ('301','349'))
  ORDER BY tidba.tranloc_ne.ccircid, tidba.tranloc_ne.cstructure

   

  I hope this help.

   

  http://www.imoveisemexposicao.com.br/imoveis-em-guarulhos
  • Marked as answer by decker dong - msft on Mar 26, 2012 02:07 AM