Soes-150911, MySQL

Language: Layout:

CREATE TABLE seccion(
  id INTEGER(10) NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(20),
  orden INTEGER(10),
  PRIMARY KEY(id)
)\\

CREATE TABLE ordenseccion(
  id INTEGER(10) NOT NULL AUTO_INCREMENT,
  usuarioid INTEGER(10),
  tipo VARCHAR(20),
  seccionid INTEGER(10),
  orden INTEGER(10),
  PRIMARY KEY(id)
)\\

INSERT INTO seccion (nombre, orden) VALUES ('A', 1), ('B', 2), ('C', 3)\\

INSERT INTO ordenseccion (usuarioid, tipo, seccionid, orden) 
VALUES (1234, 'PERSONAL', 1, 3),
       (1234, 'PERSONAL', 2, 2),
       (1234, 'PERSONAL', 3, 1),
       (5678, 'PERSONAL', 1, 1),
       (5678, 'PERSONAL', 2, 3),
       (5678, 'PERSONAL', 3, 2),
       (5678, 'DEPARTAMENTO', 1, 2),
       (5678, 'DEPARTAMENTO', 2, 1),
       (5678, 'DEPARTAMENTO', 3, 3)\\

Select GROUP_CONCAT('       SUM(CASE WHEN o.orden=', rownum, ' THEN 1 ELSE 0 END) as POS',rownum separator ',\n')
    INTO @SQL
    from (SELECT @rownum:=@rownum+1 rownum, 
                 t.*
                 FROM (SELECT @rownum:=0) r, 
                       seccion t 
                 order by id) as t1\\

set @SQL = CONCAT('SELECT s.nombre as  \'SECCION\',\n',  @SQL, '\n       from (SELECT DISTINCT tipo FROM ordenseccion) t\n       CROSS JOIN seccion s\n       left join ordenseccion o\n            ON o.seccionid = s.id  and o.tipo = t.tipo\n       WHERE t.tipo=\'PERSONAL\'\n       GROUP BY t.tipo, s.nombre') \\

PREPARE stmt1 FROM @SQL\\
EXECUTE stmt1\\ 
DEALLOCATE PREPARE stmt1\\
            
DROP TABLE seccion\\       
DROP TABLE ordenseccion\\


absolute service time: 0,38 sec