---
title : 動態式表格與資料表設計
tags: 動態式表格、EditTable.js、動態式表格資料表
creat-date: 2022-02-16
update_date : 2022-02-16
---

---
# 動態式表格與資料表設計
---

## 一、動態式表格 EditTable.js

**參考 EditTable.js：https://github.com/jdorn/EditTable**





## 二、資料表設計

**(1)系統資料對應畫面**


**(2)資料表名稱**

SELECT * 
FROM   dataset_metadata 
WHERE  title='臺中市社會救助補助項目


**(3)資料表欄位**

SELECT * 
FROM   dataset_column 
WHERE  metadatano=272


**(4)資料內容**

SELECT * 
FROM   dataset_columnvalue
WHERE  columnno=6647 
ORDER BY itemno


## 三、資料反轉

**(1)建立view，將欄位和資料內容整合成一張表**

SELECT col.columnno, col.fileno,col.metadatano, col.name, col.verify, col."position", val.itemno, val.value, val.orivalue, val.checkvalue
FROM dataset_column col
     LEFT JOIN dataset_columnvalue val ON col.columnno = val.columnno;


**(2)資料反轉(postgresql)**

SELECT *
FROM   crosstab(
	 'SELECT itemno, position, value
	  FROM   public.v_dataset_verify_tcc
	  WHERE  metadatano = 272
	  ORDER  BY 1,2' 
	  ) AS value ( "ItemNo" int,"補助流水號" character varying,"補助名稱" character varying,"補助內容" character varying,"補助對象年齡下限" character varying,"補助對象年齡上限" character varying,"身份1" character varying,"身份2" character varying,"收入條件每人每月上限" character varying,"動產條件每人上限" character varying,"不動產條件每戶上限" character varying,"其他條件" character varying,"應備文件" character varying,"收件洽辦單位" character varying,"聯繫方式" character varying,"詳細資訊" character varying);


**(3)資料反轉(sql)**

SELECT  ItemNo as '項次', *
FROM  (SELECT ItemNo, Position, Value 
        FROM   [SW_OpenData].[dbo].[v_DataSet]
        WHERE  MetadataNo = 272) As STable 
PIVOT (MAX(Value) FOR 
	    Position IN ([補助流水號],[補助名稱],[補助內容],[補助對象年齡下限],[補助對象年齡上限],[身份1],[身份2],[收入條件每人每月上限],[動產條件每人上限],[不動產條件每戶上限],[其他條件],[應備文件])) As PTable;