db2 将垂直数据转换为水平数据，60 Reg / Line

0sgqnhkj 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(136)

从该表中，

Name  surname  Phone
Name1 Sur1     Myphone1
Name2 Sur2     Myphone2
Name3 Sur3     Myphone3
Name4 Sur4     Myphone4
...
Name100 Sur100  Myphone100

我想以这种方式进行选择，水平放置60个寄存器行。

Name1Sur1Myphone1, ... Name60Sur60Myphone60
Name61Sur61Myphone61, ... Name100Sur100Myphone100

有人知道我该怎么做吗？谢谢-

db2

来源：https://stackoverflow.com/questions/70987518/transform-vertical-data-to-horizontal-with-60-reg-line

1条答案

按热度按时间

n3ipq98p1#

试试看：

/*
WITH 
  MYTAB (RN_, Name, surname, Phone) AS
(
  VALUES (1, 'Name1', 'Sur1', 'Myphone1')
    UNION ALL
  SELECT 
     RN_ + 1
  , 'Name'    || TRIM (CHAR (RN_+1)) 
  , 'Sur'     || TRIM (CHAR (RN_+1))
  , 'Myphone' || TRIM (CHAR (RN_+1))
  FROM MYTAB
  WHERE RN_ < 100
)

* /

SELECT LISTAGG (Name || surname || Phone, ',')  
FROM
(
SELECT ROW_NUMBER () OVER () AS RN_, Name, surname, Phone
FROM MYTAB
) T
GROUP BY (RN_ - 1) / 60
ORDER BY (RN_ - 1) / 60

赞(0）回复(0）举报 2022-11-07

我来回答

db2 将垂直数据转换为水平数据，60 Reg / Line

1条答案

相关问题

热门标签

最新问答