需求:用SQL实现行转列。如下图所示:
行显示的数据转换成列显示
实现行转列的SQL脚本如下:
SELECT
DATE_FORMAT(
LAST_DAY(
DATE_FORMAT(NOW(), '%Y-%m-%d')
),
'%Y%m%d'
) AS 业务日期,
MAX(CASE INDEX_CODE WHEN 'IND20101001' THEN PRSN_TOTAL END) AS 用户注册量,
MAX(CASE INDEX_CODE WHEN 'IND20204001' THEN PRSN_TOTAL END) AS 完成三项认证人数,
MAX(CASE INDEX_CODE WHEN 'IND20501001' THEN PRSN_TOTAL END) AS 投资人数,
MAX(CASE INDEX_CODE WHEN 'IND20501001' THEN COUNT_TIME END) AS 投资笔数,
MAX(CASE INDEX_CODE WHEN 'IND20504001' THEN PRSN_TOTAL END) AS 新客投资人数,
MAX(CASE INDEX_CODE WHEN 'IND21101001' THEN COUNT_TIME END) AS 满标个数,
MAX(CASE INDEX_CODE WHEN 'IND20501001' THEN AMOUNT END) AS 投资金额(元),
MAX(CASE INDEX_CODE WHEN 'IND20504001' THEN AMOUNT END) AS 新客投资金额(元),
MAX(CASE INDEX_CODE WHEN 'IND20502001' THEN AMOUNT END) AS 回款续投金额(元),
MAX(CASE INDEX_CODE WHEN 'IND20505001' THEN AMOUNT END) AS 充值金额(元),
MAX(CASE INDEX_CODE WHEN 'IND20701002' THEN AMOUNT END) AS 提现金额(元),
MAX(CASE INDEX_CODE WHEN 'IND21201001' THEN AMOUNT END) AS 资金净流量(元),
MAX(CASE INDEX_CODE WHEN 'IND21301001' THEN AMOUNT END) AS 站岗资金金额(元),
MAX(CASE INDEX_CODE WHEN 'IND21101001' THEN AMOUNT END) AS 成交额(元),
MAX(CASE INDEX_CODE WHEN 'IND20505001' THEN AMOUNT END) AS 回款金额(元),
MAX(CASE INDEX_CODE WHEN 'IND20506001' THEN AMOUNT END) AS 收益金额(元),
DATE_FORMAT(NOW(), '%Y%m%d') AS 数据时间
FROM DW_BDS.TID01_TRANS_INDEX_MONTH
WHERE REPORTING_CODE='TR04_OPER_002'
AND DATA_DATE = NOW()
GROUP BY DATA_DATE;