I have a contract table like this
And I want the result to look like this
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.
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
This query assumes that
Contract column will be unique within each "group". That is, there won't be two rows, for example, with
('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'