另类行转列

solomon_007 · 发表于 2016-12-27 12:27

〇〇发表于 2016-12-27 11:54
聚合函数好像有专门语法

我了解，这里只是说临时应付一下

zst007007 · 发表于 2017-1-7 21:05

Td我有个例子，回头发给你

calvinlp · 发表于 2017-1-9 12:12

zst007007 发表于 2017-1-7 21:05
Td我有个例子，回头发给你

能不能也发我个TD的例子

zst007007 · 发表于 2017-1-10 15:59

(30)行转列
CREATE VOLATILE  TABLE CMBSOP_PD_CFG_ORDER,NO LOG
AS
(SELECT CmBsOp_ID0
   ,ASPD_ECD
   ,ASPD_Nm
   ,ROW_NUMBER() OVER (PARTITION BY CmBsOp_ID ORDER BY ASPD_ECD ASC) AS ID
FROM P9KF_DATA_APP_OCRMC.CMBSOP_PD_CFG) WITH DATA
ON COMMIT PRESERVE ROWS;

.IF ERRORCODE <> 0 THEN .GOTO ERRORQUIT;

CREATE VOLATILE TABLE TMP_CMBSOP_PD_CFG,NO LOG
(
CmBsOp_ID    CHAR(16)    -- 营销商机编号
,ASPD_ECD_LIST    VARCHAR(3000) -- 可售产品编码
,ASPD_Nm_LIST VARCHAR(3000) -- 可售产品名称
)
ON COMMIT PRESERVE ROWS;
.IF ERRORCODE <> 0 THEN .GOTO ERRORQUIT;
/*注意：WITH RECURSIVE是处理迭代语法*/
INSERT INTO TMP_CMBSOP_PD_CFG(CmBsOp_ID,ASPD_ECD_LIST,ASPD_Nm_LIST)
WITH RECURSIVE TMP_TABLE(CmBsOp_ID,ASPD_ECD,ASPD_Nm,ID) AS
(
SELECT CmBsOp_ID,ASPD_ECD,ASPD_Nm,ID as id FROM CMBSOP_PD_CFG_ORDER WHERE ID =1
UNION ALL
SELECT T1.CmBsOp_ID,
   T1.ASPD_ECD||','||T2.ASPD_ECD,
   T1.ASPD_Nm||','||T2.ASPD_Nm,
   T2.ID
FROM TMP_TABLE T1
,CMBSOP_PD_CFG_ORDER T2
WHERE T1.CmBsOp_ID = T2.CmBsOp_ID
AND  T1.ID +1 = T2.ID
)SELECT CmBsOp_ID,ASPD_ECD,ASPD_Nm FROM TMP_TABLE QUALIFY ROW_NUMBER() OVER(PARTITION BY CmBsOp_ID  ORDER BY ID  DESC) = 1

zst007007 · 发表于 2017-1-10 16:00

(30)行转列
CREATE VOLATILE  TABLE CMBSOP_PD_CFG_ORDER,NO LOG -- 带序号的营销商机产品
AS
(SELECT CmBsOp_ID0
   ,ASPD_ECD
   ,ASPD_Nm
   ,ROW_NUMBER() OVER (PARTITION BY CmBsOp_ID ORDER BY ASPD_ECD ASC) AS ID
FROM P9KF_DATA_APP_OCRMC.CMBSOP_PD_CFG) WITH DATA
ON COMMIT PRESERVE ROWS;

.IF ERRORCODE <> 0 THEN .GOTO ERRORQUIT;

CREATE VOLATILE TABLE TMP_CMBSOP_PD_CFG,NO LOG  -- 营销商机产品配置临时表
(
CmBsOp_ID    CHAR(16)    -- 营销商机编号
,ASPD_ECD_LIST    VARCHAR(3000) -- 可售产品编码
,ASPD_Nm_LIST VARCHAR(3000) -- 可售产品名称
)
ON COMMIT PRESERVE ROWS;
.IF ERRORCODE <> 0 THEN .GOTO ERRORQUIT;
/*注意：WITH RECURSIVE是处理迭代语法*/
INSERT INTO TMP_CMBSOP_PD_CFG(CmBsOp_ID,ASPD_ECD_LIST,ASPD_Nm_LIST)
WITH RECURSIVE TMP_TABLE(CmBsOp_ID,ASPD_ECD,ASPD_Nm,ID) AS
(
SELECT CmBsOp_ID,ASPD_ECD,ASPD_Nm,ID as id FROM CMBSOP_PD_CFG_ORDER WHERE ID =1
UNION ALL
SELECT T1.CmBsOp_ID,
   T1.ASPD_ECD||','||T2.ASPD_ECD,
   T1.ASPD_Nm||','||T2.ASPD_Nm,
   T2.ID
FROM TMP_TABLE T1
,CMBSOP_PD_CFG_ORDER T2
WHERE T1.CmBsOp_ID = T2.CmBsOp_ID
AND  T1.ID +1 = T2.ID
)SELECT CmBsOp_ID,ASPD_ECD,ASPD_Nm FROM TMP_TABLE QUALIFY ROW_NUMBER() OVER(PARTITION BY CmBsOp_ID  ORDER BY ID  DESC) = 1