Mysql postgresql 行列转换
一、第一种
原数据表
转换后
DROP TABLE IF EXISTS tempdynamic; CREATE TEMPORARY TABLE tempdynamic ( SELECT p.fsPaymentName,sr.fsPaymentId,sh.fsShiftName,SUM(sr.fdReceMoney) as AmtTotal FROM tbsell as s INNER JOIN tbSellReceive as sr ON s.fsSellNo=sr.fsSellNo AND s.fsShopGUID=sr.fsShopGUID INNER JOIN tbpayment as p on sr.fsPaymentId =p.fsPaymentId and p.fsShopGUID=s.fsShopGUID and p.fiStatus=1 INNER JOIN tbSellCheck as sc on sc.fsSellNo=s.fsSellNo AND s.fsShopGUID=sc.fsShopGUID INNER JOIN tbshift as sh ON sc.fsShiftId=sh.fsShiftId AND sh.fiStatus=1 WHERE s.fiBillStatus=3 AND s.fsShopGUID='c5f0d5c9d8b349f493c180bcc22d9ce0' AND s.fsSellDate='2016-01-15' GROUP BY sr.fsPaymentId,sc.fsShiftId); SET @EE=''; set @str_tmp=''; SELECT @EE:=CONCAT(@EE,'SUM(IF(fsShiftName=\'',fsShiftName,'\'',',AmtTotal,0)) AS ',fsShiftName,',') as aa into @str_tmp FROM ( SELECT DISTINCT fsShiftName from tempdynamic ) A order by length(aa) desc limit 1; SET @QQ=CONCAT('SELECT ifnull(fsPaymentName,\'total\') as 科目名称 ,',@str_tmp,' SUM(AmtTotal) as 合计 FROM tempdynamic Group by fsPaymentName WITH ROLLUP'); PREPARE stmt FROM @QQ; EXECUTE stmt ; deallocate prepare stmt;
二、第二种
原数据表
转换后
1 DROP TABLE IF EXISTS tempdynamic; 2 CREATE TABLE tempdynamic ( 3 SELECT sh.fsShiftName,sh.fsShiftId, SUM(s.fdSaleAmt) as 销售金额,SUM(s.fdDiscountAmt) as 折扣金额, SUM(s.fdServiceAmt) as 服务费金额,SUM(s.fdExpAmt) as 应收金额 ,SUM(s.fdRealAmt) as 实收金额 4 FROM tbsell as s 5 INNER JOIN tbSellCheck as sc on sc.fsSellNo=s.fsSellNo AND s.fsShopGUID=sc.fsShopGUID 6 INNER JOIN tbshift as sh ON sc.fsShiftId=sh.fsShiftId AND sh.fiStatus=1 7 WHERE s.fiBillStatus=3 8 AND s.fsShopGUID='c5f0d5c9d8b349f493c180bcc22d9ce0' 9 AND s.fsSellDate='2016-01-15' 10 GROUP BY sc.fsShiftId); 11 12 DROP TABLE IF EXISTS tempdynamica; 13 CREATE TEMPORARY TABLE tempdynamica( 14 SELECT a.* FROM ( 15 SELECT fsShiftName,'销售金额' AS 科目,销售金额 AS AmtTotal FROM tempdynamic UNION ALL 16 SELECT fsShiftName,'折扣金额' AS 科目,折扣金额 AS AmtTotal FROM tempdynamic UNION ALL 17 SELECT fsShiftName,'服务费金额' AS 科目,服务费金额 AS AmtTotal FROM tempdynamic UNION ALL 18 SELECT fsShiftName,'应收金额' AS 科目,应收金额 AS AmtTotal FROM tempdynamic UNION ALL 19 SELECT fsShiftName,'实收金额' AS 科目,实收金额 AS AmtTotal FROM tempdynamic 20 ORDER BY fsShiftName,科目 DESC) as a ); 21 22 23 SET @EE=''; 24 set @str_tmp=''; 25 SELECT @EE:=CONCAT(@EE,'SUM(IF(fsShiftName=\'',fsShiftName,'\'',',AmtTotal ,0)) AS ',fsShiftName,',') as aa into @str_tmp 26 FROM ( 27 SELECT DISTINCT fsShiftName from tempdynamic 28 ) A order by length(aa) desc limit 1; 29 SET @QQ=CONCAT('SELECT ifnull(科目,\'total\') as 科目名称,',@str_tmp,'sum(AmtTotal) as 合计 FROM tempdynamica Group by 科目 WITH ROLLUP'); 30 PREPARE stmt FROM @QQ; 31 EXECUTE stmt ; 32 deallocate prepare stmt;
postgresql
原数据：
转换后：
sql:
CREATE OR REPLACE FUNCTION "public"."test"()
RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
dynsql1 VARCHAR ;
dynsql2 VARCHAR ;
dynsql3 VARCHAR ;
BEGIN
drop table if exists temp_sell;
dynsql1='CREATE TEMP TABLE temp_sell AS SELECT
bd.area_id,
bd.area_name,
bd.sell_date,
SUM (
COALESCE (bss.original_amt, 0)
) AS original_amt
FROM
bill_detail AS bd
INNER JOIN bill_settlement_summary AS bss ON bd.bill_id = bss.bill_id
WHERE
bd.sell_date BETWEEN ''2018-01-01''
AND ''2018-01-30''
AND bd.shop_guid=''143174''
-- AND area_id=''''
-- AND msection_id=''''
GROUP BY
bd.sell_date,
bd.area_id,
bd.area_name;';
EXECUTE dynsql1;
dynsql2 = '';
dynsql3 = '';
drop table if exists temp_sell_1;
CREATE TEMP TABLE temp_sell_1 AS
SELECT concat('SUM(CASE WHEN sell_date=''',sell_date,'''','then original_amt else 0 end) AS "',sell_date,'"') AS aa
FROM
(SELECT DISTINCT
sell_date
FROM
temp_sell) A
ORDER BY sell_date ASC ;
SELECT string_agg(aa, ',') INTO dynsql2 from temp_sell_1;
RAISE NOTICE 'dynsql2==[%]', dynsql2 ;
dynsql3 = CONCAT(
'SELECT area_id as 区域id,area_name as 区域名称,' ,dynsql2,
',sum(original_amt) 汇总 FROM temp_sell Group By area_id,area_name;'
);
RAISE NOTICE 'dynsql3==[%]', dynsql3 ;
END ;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100
;
SELECT test();
SELECT area_id as 区域id,area_name as 区域名称,SUM(CASE WHEN sell_date='2018-01-12'then original_amt else 0 end) AS "2018-01-12",SUM(CASE WHEN sell_date='2018-01-18'then original_amt else 0 end) AS "2018-01-18",SUM(CASE WHEN sell_date='2018-01-19'then original_amt else 0 end) AS "2018-01-19",SUM(CASE WHEN sell_date='2018-01-25'then original_amt else 0 end) AS "2018-01-25",sum(original_amt) 汇总 FROM temp_sell Group By area_id,area_name;
第二种方案：
参考： http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/
使用函数如下：
CREATE OR REPLACE FUNCTION "public"."pivotcode"("tablename" varchar, "rowc" varchar, "rowctype" varchar, "groupby" varchar, "colc" varchar, "cellc" varchar, "celldatatype" varchar) RETURNS "pg_catalog"."void" AS $BODY$ DECLARE rec record ; dynsql1 VARCHAR ; dynsql2 VARCHAR ; countrows INT; columnlist VARCHAR ; BEGIN drop table if exists temp_sell_result; dynsql1 = 'select count(1) from ' || tablename || ';' ; EXECUTE dynsql1 INTO countrows ; IF countrows=0 THEN dynsql1='CREATE TEMP TABLE temp_sell_result AS select * from ' || tablename || ';'; EXECUTE dynsql1; ELSE -- 1. retrieve list of column names. dynsql1 = 'select string_agg(distinct ''"''||' || colc || '||''" ' || celldatatype || ''','','' order by ''"''||' || colc || '||''" ' || celldatatype || ''') from ' || tablename || ';' ; --RAISE NOTICE '%', dynsql1 ; EXECUTE dynsql1 INTO columnlist ; -- 2. set up the crosstab query dynsql2 = 'CREATE TEMP TABLE temp_sell_result AS select * from crosstab ( ''select ' || rowc || ',' || colc || ',' || cellc || ' from ' || tablename || ' group by '||groupby||' order by '||groupby||''', ''select distinct ' || colc || ' from ' || tablename || ' order by 1'' ) as newtable ( ' || rowctype || ',' || columnlist || ' );' ; EXECUTE dynsql2 ; END IF; END ; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ; ALTER FUNCTION "public"."pivotcode"("tablename" varchar, "rowc" varchar, "rowctype" varchar, "groupby" varchar, "colc" varchar, "cellc" varchar, "celldatatype" varchar) OWNER TO "winposdev";
