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

zzl001

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