Last post Mar 20, 2012 11:59 PM by gimimex

None

0 Points

1 Post

### Combining Common Rows

Mar 20, 2012 09:10 PM|jay.emery|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

Contributor

4800 Points

981 Posts

### Re: Combining Common Rows

Mar 20, 2012 11:59 PM|gimimex|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.