I have a table where the data is like below:
and I want to get the output like:
that means I want to select a row those many times what is mentioned in product_sold column.
This is usually done with a Cross Join to a table with numbers 1 to n in it.
But can simply abuse EXPAND ON:
SELECT * FROM myTable EXPAND ON PERIOD (DATE '0001-01-01', DATE '0001-01-01' + Product_Sold) AS pd
Please have below solution. This may help you to get the result.
CREATE SET TABLE TEMP1_WRK ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
PRIMARY INDEX ( P_NAME );
INSERT INTO FINANCIAL.TEMP1_WRK VALUES ('A',2);
INSERT INTO FINANCIAL.TEMP1_WRK VALUES ('B',3);
INSERT INTO FINANCIAL.TEMP1_WRK VALUES ('C',6);
WITH RECURSIVE T1 (P_C1,P_N1,CNT)
SELECT P_NAME, P_SOLD AS P_N1 , 1 AS CNT FROM
SELECT P_C1,P_N1 FROM T1
ORDER BY 1;