NeoS NeoS -4 years ago 122
MySQL Question

Custom Group tables

I have a contract table like this

Table

And I want the result to look like this

Result

What query should I use to retrieve the result like the example? I was trying to use group query but it still doesn't do what I want.

Answer Source

Something like this will return the specified result. It's not clear what columns you want to "match" on, I used the first four columns, because those are identical on the rows you show "grouped" together.

The "trick" is to use a GROUP BY to get the distinct list of rows you want to return. You can the use outer joins to pick out the individual rows, to match to the rows returned from the inline view (aliased as g).

This query assumes that Contract column will be unique within each "group". That is, there won't be two rows, for example, with Contract='I' for ('ENG','SWD','ABCDf','2012-11-06'). (In the sample data, it is unique, but we haven't been given any guarantee that it is unique.)

SELECT g.departemen
     , g.section
     , g.name
     , g.start
     , i.`end contract` AS `End Contract I`
     , p.`end contract` AS `End Contract P`
     , q.`end contract` AS `End Contract II`
  FROM ( SELECT t.departemen
              , t.section
              , t.name
              , t.start
           FROM mytable t
          GROUP
             BY t.departemen
              , t.section
              , t.name
              , t.start
       ) g

  LEFT
  JOIN mytable i
    ON i.departemen = g.departemen
   AND i.section    = g.section
   AND i.name       = g.name
   AND i.start      = g.start
   AND i.contract   = 'I'

  LEFT
  JOIN mytable p
    ON p.departemen = g.departemen
   AND p.section    = g.section
   AND p.name       = g.name
   AND p.start      = g.start
   AND p.contract   = 'P'

  LEFT
  JOIN mytable q
    ON q.departemen = g.departemen
   AND q.section    = g.section
   AND q.name       = g.name
   AND q.start      = g.start
   AND q.contract   = 'II'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download