0

Hello Stackoverflow Community,

i have a MYSQL table like this:

+---------------------+
| id  cid  tid  tte   |
+---------------------+
| 1   11   33   580   |
| 2   44   33   581   |
| 3   55   44   582   |
| 4   77   44   583   |
+---------------------+

I would like to create a html table like this:

+----------------------+
| tid   cid1 cid2 ...  |
+----------------------+
| 33    580  581  ...  |
| 44    582  583  ...  |
+----------------------+

How is this possible? I googled few hours but i didn't found anything for my program.

Tobias JUng
  • 13
  • 3

2 Answers2

0

Query:

select tid,
max(case when tid='33' then '580' else '582' end) cid1,
max(case when tid='44' then '583' else '581' end) cid2
from 'yourtable'
group by tid
Vinoth Raj
  • 296
  • 1
  • 14
0
DROP PROCEDURE IF EXISTS pro;


CREATE PROCEDURE pro() BEGIN DECLARE t int;
DECLARE c int;
DECLARE c_field int;
DECLARE old_t int;
DECLARE v_sql varchar(200);
DECLARE done int DEFAULT FALSE;
DECLARE cur
CURSOR
FOR
SELECT tid,
tte
FROM tab1
ORDER BY tid;
DECLARE CONTINUE
HANDLER
FOR NOT FOUND
SET done = TRUE;
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2(tid int, cid1 int, cid2 int, cid3 int);
SET old_t = 0;
OPEN cur;
read_loop: LOOP FETCH cur INTO t,
    c;
IF done THEN leave read_loop;
END IF;
IF old_t < > t THEN
SET old_t = t;
SET c_field = 1;
INSERT INTO tab2(tid, cid1)
VALUES(t,
    c);
ELSE
SET c_field = c_field + 1;
SET v_sql = concat('update tab2 set cid', c_field, '=', c, ' where tid=', t);
SET@ v_sql = v_sql;
PREPARE stmt
FROM@ v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
COMMIT;
END LOOP;
CLOSE cur;
SELECT *
    FROM tab2;
END;
CALL pro
sam
  • 1
  • 1