DB2中 同一个表中多条数据组合成一条数据 （一条SQL实现）

最新推荐文章于 2023-05-05 16:57:54 发布
最新推荐文章于 2023-05-05 16:57:54 发布
阅读量635 收藏
点赞数


SELECT
(
SELECT PERSONNAME FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=1
) AS B
) AS PERSONNAME1
, (
SELECT GENDER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=1
) AS B
) AS GENDER1
, (
SELECT BIRTHDAY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=1
) AS B
) AS BIRTHDAY1
, (
SELECT NATIONALITY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=1
) AS B
) AS NATIONALITY1
, (
SELECT PASSPORT_NUMBER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=1
) AS B
) AS PASSPORT_NUMBER1
, (
SELECT PERSONNAME FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=2
) AS B
) AS PERSONNAME2
, (
SELECT GENDER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=2
) AS B
) AS GENDER2
, (
SELECT BIRTHDAY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=2
) AS B
) AS BIRTHDAY2
, (
SELECT NATIONALITY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=2
) AS B
) AS NATIONALITY2
, (
SELECT PASSPORT_NUMBER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=1
) AS B
) AS PASSPORT_NUMBER2
, (
SELECT PERSONNAME FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=3
) AS B
) AS PERSONNAME3
, (
SELECT GENDER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=3
) AS B
) AS GENDER3
, (
SELECT BIRTHDAY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=3
) AS B
) AS BIRTHDAY3
, (
SELECT NATIONALITY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=3
) AS B
) AS NATIONALITY3
, (
SELECT PASSPORT_NUMBER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=1
) AS B
) AS PASSPORT_NUMBER3
, (
SELECT PERSONNAME FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=4
) AS B
) AS PERSONNAME4
, (
SELECT GENDER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=4
) AS B
) AS GENDER4
, (
SELECT BIRTHDAY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=4
) AS B
) AS BIRTHDAY4
, (
SELECT NATIONALITY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=4
) AS B
) AS NATIONALITY4
, (
SELECT PASSPORT_NUMBER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=1
) AS B
) AS PASSPORT_NUMBER4
, (
SELECT PERSONNAME FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=5
) AS B
) AS PERSONNAME5
, (
SELECT GENDER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=5
) AS B
) AS GENDER5
, (
SELECT BIRTHDAY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=5
) AS B
) AS BIRTHDAY5
, (
SELECT NATIONALITY FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=5
) AS B
) AS NATIONALITY5
, (
SELECT PASSPORT_NUMBER FROM
(
select "PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER" from ( select ROWNUMBER() OVER() as ROWID ,"PERSONNAME", "GENDER",
"BIRTHDAY", "NATIONALITY", "PASSPORT_NUMBER"
from T_GA_PERSONLIST where GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
ORDER BY RECORD_GUID ) as a where a.ROWID=1
) AS B
) AS PASSPORT_NUMBER5
from T_GA_PERSONLIST P
WHERE GROUPGUID= '4a43c92c-9db6-41d7-b23b-ba8180b04adf'
FETCH FIRST 1 ROW ONLY
iteye_4185
关注
DB2同一字段字符多条记录合并
qq_37932082的博客
03-05 7360
DB2将多行记录合并成一行 如：CREATE TABLE LX_TMP1(ID VARCHAR(100),OLDNAME VARCHAR(100));INSERT INTO LX_TMP1 VALUES('LUYONG','A');INSERT INTO LX_TMP1 VALUES('LUYONG','B');INSERT INTO LX_TMP1 VALUES('LU','1');INSERT...
sqlserver 把两个sql查询语句查询出来的两张表合并成一张表
weixin_30919919的博客
07-13 6835
第一个sql语句 select companyname gsmc,zb zhibiao from t_gsndzb left join t_companycode on t_gsndzb.gsbh=t_companycode.companyid 查询结果： 第二个sql语句 SELECT min(companyname) gsmc,cast(round(sum...
DB2将多行记录合并成一行
果子爸聊技术
12-17 5543
DB2将多条记录的同一个字段合并，比如： A B 1 a 1 b 1 c 2 d 2 f 转化成如下： A B 1 a,b,c 2 d,f 在网上找了很多资料，现在分享一个别人给出的解决办法： 前提是数据库支持XML select A,replace(replace(xml2clob(xmlagg(x...
DB2 将多行记录合并成一行
John的博客
05-05 1529
DB2 将多行记录合并成一行
sql语句：db2根据某字段合并多行数据，将相同组中多行例合并到一个字段
weixin_43769531的博客
01-08 4723
原数据 合并后数据 按name字段分组，合并house，aaa字段* 合并数据后的缺点就是有重复的的，这个是本人接受不了的，限于技术有限，只能凑合的用了，业务要求多，技术不过关哎。。。 建表 CREATE TABLE ttable ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY , name varchar(100), house varchar(100), aaa varchar(100), PRIMARY KEY (id) ); I
DB2多行转一行【XML方式】
weixin_45242775的博客
10-20 1103
–DB2多行转一行【XML方式】 –分组然后合并，然后去除XML标签 SELECT replace(replace(replace(xml2clob(xmlagg(xmlelement(name A, 【字段】))),’’, ‘【分隔符】’),’’,’’),’’,’’) from 【表】 group by 【分组字段】 –示例 SELECT hy.HYDH,hy.HYMC ,replace(replace(replace(xml2clob(xmlagg(xmlelement(name A,jg.JGMC))
(sql查询当天,周,月数据)(多列组合查询)(窗口函数排名rank、dense_rank、row_number)(select查询使用条件逻辑)（SQL 行转列，列转行）
二八定律法则
05-16 1699
(sql查询当天,周,月数据)(多列组合查询)(窗口函数排名rank、dense_rank、row_number)(select查询使用条件逻辑)（SQL 行转列，列转行）
mysql一行多列变成一列多行_MySql必备基础知识（一）
weixin_39950057的博客
11-20 1022
1　数据库概念(了解)1.1　什么是数据库数据库就是用来存储和管理数据的仓库！数据库存储数据的优先：可存储大量数据；方便检索；保持数据的一致性、完整性；安全，可共享；通过组合分析，可产生新数据。1.2　数据库的发展历程没有数据库，使用磁盘文件存储数据；层次结构模型数据库；网状结构模型数据库；关系结构模型数据库：使用二维表格来存储数据；关系-对象模型数据库；　　MySQL就是关系型数据库！1.3　常...
sql里将重复行数据合并为一行数据使用逗号进行分隔
09-10
sql里将重复行数据合并为一行,将多行fieldname字段的内容串联起来，用逗号分隔，接下来为大家介绍下详细的实现sql语句，希望对你有所帮助
DB2多行结果合并到一行排列输出（类似递归）
zl520878的博客
09-28 4729
需实现这样的功能，把同样接单量的人员姓名通过逗号分隔存到一个字段中 1 张三 1 李四 1 王五 处理后： 1  张三，李四，王五   -------------------------------------------分隔线----------------------------------------------   DW_RPT.TDM_ONLINESERVICE_CH...
DB2 SQL 递归实现多行合并
bantaoti1130的博客
10-31 270
最终效果 原始数据： 转换脚本： WITH post_a AS ( SELECT DISTINCT T.EMP_NO,S.CODE_ FROM inscndb.DTFMA000_EMP_POST T ,VIEW_BI_POST S WHERE T.POST||'-'||POST2 = S.POST ), post_b AS (SELECT emp_no...
ORACLE / DB2数据库列转行（实现字段合并)
Sakurazh的博客
10-24 859
ORACLE数据库列转行（实现字段合并） SELECT T.SCATEG,TO_CHAR(wm_concat(SNAME)) FROM STAR T GROUP BY T.SCATEG; DB2数据库列转行（实现字段合并） SELECT LISTAGG(T.SNAME,’,’) FROM STAR T GROUP BY T.SCATEG; ...
DB2多行转一行【XML2CLOB方式】
yang的博客
08-15 1586
场景： table1和table2是一对多关系，想要在同一条SQL中把table1和table2的数据全部查出来 方案： 通过DB2的XML2CLOB将多的表合并成一个column，然后在结果中通过Java代码解析成集合 SELECT T1.ID, T2.NAME, (SELECT XML2CLOB(XMLAGG(XMLELEMENT(NAME \"X\",T2.CARD_ID))) FROM T...
oracle数据库将多个列的查询结果集合并到一行中
热门推荐
500-Internal Server Error
01-03 3万+
oracle数据库使用系统函数将多个列的查询结果集合并到一行中。
1、DB2将一列转成一行并用逗号隔开，db2获取当前时间，生产uuid；2、mysql两张表关联字段得数据修改和联系；3、DB2存储过程实现，通过判断数据库是否重复，返回不同的结果
weixin_50235904的博客
05-14 2263
DB2将一列转成一行并用逗号隔开， DB2存储过程实现，通过判断数据库是否重复，返回不同的结果
db2 sql普通查询时查询结果为空时展示一条空数据
最新发布
06-02
在DB2中，如果普通查询结果为空，可以使用UNION ALL语句来添加一行空数据。具体做法如下： ``` SELECT col1, col2, col3 FROM table_name WHERE condition UNION ALL SELECT NULL, NULL, NULL FROM SYSIBM.SYSDUMMY...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值