PostGrepSql行转列

最新推荐文章于 2025-04-11 16:16:36 发布

xiao白跳大神

最新推荐文章于 2025-04-11 16:16:36 发布

阅读量702

点赞数

分类专栏：工作文章标签：线程池简单使用 spring boot 后端

本文链接：https://blog.youkuaiyun.com/weixin_42183336/article/details/121626969

版权

工作专栏收录该内容

7 篇文章

订阅专栏

建表语句:

CREATE TABLE public.order_t (
	order_name varchar(255) NULL,
	order_id varchar(255) NULL,
	oder_time varchar(255) NULL,
	amount int4 NULL,
	province_id int4 NULL,
	test_date varchar NULL
);

测试数据:

INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('奇瑞控股-系统', 'DHDB190601022', '2019/6/1', 484, 340000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('绿城服务财务共享项目', 'DHDB210101005', '2020/12/1', 330, 330000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('浙江交投咨询项目', 'DHDB191001058', '2019/10/1', 222, 330000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('绿城服务收入结算平台系统项目', 'DHDB210601102', '2021/6/1', 321, 330000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('浙江交投工作量评估', 'DHDB210401140', '2021/4/1', 85, 330000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('绿城服务共享-咨询', 'DHDB210401032', '2021/4/1', 273, 330000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('绿城服务财务共享项目', 'DHDB210101005', '2021/1/1', 37, 330000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('中汽中心电子影像项目', 'DHBB200301032', '2020/3/1', 37, 120000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('一汽丰田系统项目', 'DHBB210101037', '2021/1/1', 447, 120000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('奇瑞控股-咨询', 'DHDB180601009', '2018/8/1', 370, 340000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('奇瑞控股-系统', 'DHDB190601022', '2018/10/1', 308, 340000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('奇瑞控股-系统', 'DHDB190601022', '2018/12/1', 307, 340000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('test13	p', '456', NULL, NULL, NULL, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES(' 奇瑞系统项目三期 ', 'DHDB200801098', '2020/8/1', 464, 340000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('咋就放那><>咋就放那', '789', '2022/12/12', 10, 100000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES(' .,test12,,..>>//3 ><>@#$%^&*，,。.？咋就放那?！!：:；;“”‘’（）()[]【||】{}…、─-－-~_—"&*,,..>>//3\\ ><>@#$%^&*，,。.？?！!：:；;“”‘’（）()[]【】{}…─-－-~_—"&*  ', '123', '2022/12/12', 10, 100000, '2012-12-12 12:12:12');
INSERT INTO order_t (order_name, order_id, oder_time, amount, province_id, test_date) VALUES('奇 瑞 控 股- 系 统', 'DHDB190601022', '2019/3/1', 69, 340000, '2012-12-12 12:12:12');

将一个数据表某几个列转为行数据，并且要求顺序一致，如下图(图1转换前，图2转换后)

如果不要求顺序可以直接使用union处理

将图1的order_name和order_id转为表数据

图1：

图2：

以下是执行的脚本：

SELECT
	oder_time, --表原始字段
	amount, --表原始字段
	province_id,--表原始字段
	test_date, --表原始字段
	'order_name' :: VARCHAR ( 100 ) ascoIdent, --将order_name列名转成列ascoIdent的数据
	order_name :: VARCHAR ( 300 ) AS valIdent, --将order_name的值转成列ascoIdent的数据
	ROW_NUMBER ( ) OVER ( ORDER BY - 1 ) AS ids, --按照正常查询的顺序自增
	1 AS ida --默认第一条数据给1，第二条给2
FROM
	order_t 
    UNION ALL --合并
SELECT
	oder_time,
	amount,
	province_id,
	test_date,
	'order_id' :: VARCHAR ( 100 ) ascoIdent,
	order_id :: VARCHAR ( 300 ) AS valIdent,
	ROW_NUMBER ( ) OVER ( ORDER BY - 1 ) AS ids,
	2 AS ida 
FROM
	order_t 
ORDER BY
	ids,--排序
	ida --排序

将所有的列都转为数据类似，如下：

脚本：

SELECT
	'order_name' :: VARCHAR ( 100 ) ascoIdent,
	order_name :: VARCHAR ( 300 ) AS valIdent,
	ROW_NUMBER ( ) OVER ( ORDER BY - 1 ) AS ids,
	1 AS ida 
FROM
	order_t UNION ALL
SELECT
	'order_id' :: VARCHAR ( 100 ) ascoIdent,
	order_id :: VARCHAR ( 300 ) AS valIdent,
	ROW_NUMBER ( ) OVER ( ORDER BY - 1 ) AS ids,
	2 AS ida 
FROM
	order_t UNION ALL
SELECT
	'oder_time' :: VARCHAR ( 100 ) ascoIdent,
	oder_time :: VARCHAR ( 300 ) AS valIdent,
	ROW_NUMBER ( ) OVER ( ORDER BY - 1 ) AS ids,
	3 AS ida 
FROM
	order_t UNION ALL
SELECT
	'amount' :: VARCHAR ( 100 ) ascoIdent,
	amount :: VARCHAR ( 300 ) AS valIdent,
	ROW_NUMBER ( ) OVER ( ORDER BY - 1 ) AS ids,
	4 AS ida 
FROM
	order_t UNION ALL
SELECT
	'province_id' :: VARCHAR ( 100 ) ascoIdent,
	province_id :: VARCHAR ( 300 ) AS valIdent,
	ROW_NUMBER ( ) OVER ( ORDER BY - 1 ) AS ids,
	5 AS ida 
FROM
	order_t UNION ALL
SELECT
	'test_date' :: VARCHAR ( 100 ) ascoIdent,
	test_date :: VARCHAR ( 300 ) AS valIdent,
	ROW_NUMBER ( ) OVER ( ORDER BY - 1 ) AS ids,
	6 AS ida 
FROM
	order_t 
ORDER BY
	ids,
	ida