Hi ream,
Do you have high performance column to row and row to column rotation solution like following SQL?
--3 sec
WITH DATA_TABLE AS (
SELECT T.PERIOD_ID, T2.REGION_ORG_CN_NAME AS REGION_NAME,T2.COUNTRY_CN_NAME AS COUNTRY_NAME,T3.CUST_EN_NAME AS ACCOUNT_NAME_D,T4.EXTERNAL_NAME AS PRODUCT,T4.PROD_CN_NAME AS PRODUCT_MODEL,T6.COLOR AS PRODUCT_COLOR,T6.PHONERAM AS PRODUCT_RAM,T6.PHONEROM AS PRODUCT_ROM,
SUM(t.TOTAL_SELL_IN_QTY) SI_TOTAL_QTY,
SUM(t.TOTAL_OUT_QTY) SO_TOTAL_QTY,
SUM(t.SELL_IN_QTY) SI,SUM(t.OUT_QTY) SO,
SUM(t.INV0_QTY) INV,
ROUND(SUM(t.INV0_QTY)/NULLIF(SUM(t.OUT_AVE_QTY),0),0) DOS,
ROUND(SUM(t.LEAD_TIME)/NULLIF(SUM(t.OUT_QTY),0),0) Lead_Time,
SUM(t.STR_SELL_THRU_QTY) ST,
SUM(t.STR_INV1_QTY) INV1,
ROUND(SUM(t.STR_INV1_QTY)/NULLIF(SUM(t.STR_OUT_AVG_QTY),0),0) DOS1
FROM RPT_PSI_SO1_DOS_DAY_N_NEW_F T,
RPT_TML_PSI_REGION_C_PRV_REL_D T2,
RPT_TML_PSI_CUSTOMER_D T3,
RPT_TML_PRODUCT_D T4,
RPT_DIM_TML_PARTNER_CHANNEL_D T5,
RPT_TML_PSI_ITEM_ATTR_EXT_F T6
WHERE T.CUST_ACCOUNT_CODE = T3.CUST_ACCOUNT_NUM
AND T.PROD_KEY = T4.PROD_KEY
AND T.SIGN_COUNTRY_PROVINCE_CODE = T2.COUNTRY_PROVINCE_CODE
AND T.ITEM_CODE = T6.ITEM_CODE
AND T2.SCD_ACTIVE_IND = 1
AND (T4.LV0_PROD_LIST_CN_NAME IN ('消费者') OR T4.LV0_PROD_LIST_CODE IN ('SNULL'))
AND T.CHANNEL_ID = T5.PARTNER_CHANNEL_CODE
AND INSTR(',In Sales,' , ','||T.SALES_STATUS||',') > 0 AND t.period_id BETWEEN 20170101 AND 20170110
GROUP BY T2.REGION_ORG_CN_NAME,T2.COUNTRY_CN_NAME,T3.CUST_EN_NAME,T4.EXTERNAL_NAME,T4.PROD_CN_NAME,T6.COLOR,T6.PHONERAM,T6.PHONEROM, T.PERIOD_ID
),
--3sec+
DATA_TABLE1 AS
(
SELECT period_id,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,'Sell In' AS PSI_TYPE, SI AS QTY FROM DATA_TABLE
union all
SELECT period_id,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,'Sell Out' AS PSI_TYPE, SO AS QTY FROM DATA_TABLE
UNION ALL
SELECT period_id,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,'Sell Thru' AS PSI_TYPE, ST AS QTY FROM DATA_TABLE
UNION ALL
SELECT period_id,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,'Inventory' AS PSI_TYPE, INV AS QTY FROM DATA_TABLE
UNION ALL
SELECT period_id,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,'DOS' AS PSI_TYPE, DOS AS QTY FROM DATA_TABLE
UNION ALL
SELECT period_id,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,'Inventory1' AS PSI_TYPE, INV1 AS QTY FROM DATA_TABLE
UNION ALL
SELECT period_id,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,'DOS1' AS PSI_TYPE, DOS1 AS QTY FROM DATA_TABLE
UNION ALL
SELECT period_id,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,'Lead Time' AS PSI_TYPE, LEAD_TIME AS QTY FROM DATA_TABLE
)
,
--4 sec
DATA_TABLE4 AS(
SELECT 1 as columntotal, period_id,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,PSI_TYPE,QTY,
case when PERIOD_ID= 11111111 then
qty
else
0
end AS LIFECYCLE,
case when PERIOD_ID= 20170101 then
qty
else
0
end AS COLUMN1,
case when PERIOD_ID= 20170102 then -- TO_NUMBER(TO_CHAR(TO_DATE('20170101','YYYYMMDD')+ 1,'YYYYMMDD')) then
qty
else
0
end AS COLUMN2,
case when PERIOD_ID= 20170103 then -- TO_NUMBER(TO_CHAR(TO_DATE('20170101','YYYYMMDD')+ 2,'YYYYMMDD')) then
qty
else
0
end AS COLUMN3,
case when PERIOD_ID= 20170104 then --TO_NUMBER(TO_CHAR(TO_DATE('20170101','YYYYMMDD')+ 3,'YYYYMMDD')) then
qty
else
0
end AS COLUMN4,
case when PERIOD_ID=20170105 then -- TO_NUMBER(TO_CHAR(TO_DATE('20170101','YYYYMMDD')+ 4,'YYYYMMDD')) then
qty
else
0
end AS COLUMN5,
case when PERIOD_ID=20170106 then -- TO_NUMBER(TO_CHAR(TO_DATE('20170101','YYYYMMDD')+ 5,'YYYYMMDD')) then
qty
else
0
end AS COLUMN6,
case when PERIOD_ID= 20170107 then --TO_NUMBER(TO_CHAR(TO_DATE('20170101','YYYYMMDD')+ 6,'YYYYMMDD')) then
qty
else
0
end AS COLUMN7,
case when PERIOD_ID= 20170108 then --TO_NUMBER(TO_CHAR(TO_DATE('20170101','YYYYMMDD')+ 7,'YYYYMMDD')) then
qty
else
0
end AS COLUMN8,
case when PERIOD_ID= 20170109 then --TO_NUMBER(TO_CHAR(TO_DATE('20170101','YYYYMMDD')+ 8,'YYYYMMDD')) then
qty
else
0
end AS COLUMN9,
case when PERIOD_ID= 20170102 then --TO_NUMBER(TO_CHAR(TO_DATE('20170101','YYYYMMDD')+ 9,'YYYYMMDD')) then
qty
else
0
end AS COLUMN10
FROM DATA_TABLE1
),
DATA_TABLE5 AS (
select ifnull(columntotal,0) columntotal, REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,
PSI_TYPE ,
sum(LIFECYCLE) LIFECYCLE,sum(0) AS rowtotal,
sum(column1) column1 ,
sum(column2) column2 ,
sum(column3) column3 ,
sum(column4) column4 ,
sum(column5) column5 ,
sum(column6) column6 ,
sum(column7) column7 ,
sum(column8) column8 ,
sum(column9) column9 ,
sum(column10) column10
from DATA_TABLE4
GROUP BY REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,
PSI_TYPE, columntotal,PSI_TYPE
)
SELECT T.*,
'20170101,20170102,20170103,20170104,20170105,20170106,20170107,20170108,20170109,20170110' AS DATE1,
'Region,Country,Account Name(D),Product,Product Model,Product Color,Product Ram,Product Rom,PSI Type' AS DISPLAY1,
'REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,PSI_TYPE' AS DISPLAY2
FROM (
SELECT * FROM (SELECT *
FROM (SELECT T.* , ROW_NUMBER() OVER () AS RN1
FROM (select * from DATA_TABLE5 where columntotal =1 order by columntotal,REGION_NAME,COUNTRY_NAME,ACCOUNT_NAME_D,PRODUCT,PRODUCT_MODEL,PRODUCT_COLOR,PRODUCT_RAM,PRODUCT_ROM,
decode(PSI_TYPE,
'Sell In',
1,
'Sell Thru',
2,
'Sell Out',
3,
'Inventory',
4,
'DOS',
5,
'Inventory1',
6,
'DOS1',
7,
'Lead Time',
8,
9) ) T) T LIMIT 50) T where RN1 >= 1
) T
