clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591537 fiddles created (45736 in the last week).

DROP FUNCTION IF EXISTS ithelp_20190312_fun1(); DROP FUNCTION IF EXISTS ithelp_20190312_fun2(r int); DROP FUNCTION IF EXISTS ithelp_20190312_fun3(p int); DROP TABLE IF EXISTS ithelp_20190312_t0; CREATE TABLE ithelp_20190312_t0(PERS_COD VARCHAR,PERS_NAME VARCHAR,ATTND_DAT DATE,ON_TIME TIME); INSERT INTO ithelp_20190312_t0 VALUES ('A001','王大明','2019/2/26','09:09'), ('A001','王大明','2019/2/26','15:31'), ('A001','王大明','2019/2/26','16:20'), ('A001','王大明','2019/2/26','20:32'), ('A001','張小馬','2019/2/26','09:02'), ('A001','張小馬','2019/2/26','19:37') ; select * from ithelp_20190312_t0
6 rows affected
pers_cod pers_name attnd_dat on_time
A001 王大明 2019-02-26 09:09:00
A001 王大明 2019-02-26 15:31:00
A001 王大明 2019-02-26 16:20:00
A001 王大明 2019-02-26 20:32:00
A001 張小馬 2019-02-26 09:02:00
A001 張小馬 2019-02-26 19:37:00
 hidden batch(es)


DROP FUNCTION IF EXISTS ithelp_20190312_fun1(); CREATE OR REPLACE FUNCTION ithelp_20190312_fun1() RETURNS INTEGER as $BODY$ BEGIN RETURN tag from ( select MAX(tag) over (PARTITION BY 1) tag from( select *, row_number() over (PARTITION BY pers_name order by on_time) tag from ithelp_20190312_t0 ) A ) B group by tag ; END $BODY$ LANGUAGE 'plpgsql'; select ithelp_20190312_fun1()
ithelp_20190312_fun1
4
 hidden batch(es)


DROP FUNCTION IF EXISTS ithelp_20190312_fun2(s int); CREATE OR REPLACE FUNCTION ithelp_20190312_fun2(s int) RETURNS void AS $BODY$ BEGIN FOR r IN 1..s LOOP EXECUTE 'DROP TABLE IF EXISTS ithelp_20190312_t'||r||'; CREATE TABLE ithelp_20190312_t'||r||' AS select *, MAX(CASE WHEN '||r||' = time_tag'||r||' THEN on_time ELSE null END) OVER (PARTITION BY pers_name) 時間'||r||' from( select *, row_number() over (PARTITION BY pers_name ORDER BY on_time) time_tag'||r||' from ithelp_20190312_t'||r-1||') a'; END LOOP; END $BODY$ LANGUAGE 'plpgsql'; select ithelp_20190312_fun2(ithelp_20190312_fun1()) ;
ithelp_20190312_fun2
 hidden batch(es)


DROP FUNCTION IF EXISTS ithelp_20190312_fun3(p int); CREATE OR REPLACE FUNCTION ithelp_20190312_fun3(p int) RETURNS void AS $BODY$ BEGIN EXECUTE 'DROP TABLE IF EXISTS ithelp_20190312_t'||p+1||'; CREATE TABLE ithelp_20190312_t'||p+1||' AS SELECT pers_cod, pers_name, attnd_dat, 時間1, 時間2, 時間3, 時間4 FROM ithelp_20190312_t'||p||' group by pers_cod, pers_name, attnd_dat, 時間1, 時間2, 時間3, 時間4'; END $BODY$ LANGUAGE 'plpgsql'; select * from ithelp_20190312_fun3(ithelp_20190312_fun1()) ;
ithelp_20190312_fun3
 hidden batch(es)


select * from ithelp_20190312_t5
pers_cod pers_name attnd_dat 時間1 時間2 時間3 時間4
A001 張小馬 2019-02-26 09:02:00 19:37:00
A001 王大明 2019-02-26 09:09:00 15:31:00 16:20:00 20:32:00
 hidden batch(es)