Need help in SQL

Database
Highlighted
Enthusiast

Need help in SQL

I have a table where the data is like below:

Product_Name

Product_Sold

A

2

B

3

 

and I want to get the output like:

 

Product_Name

Product_Sold

A

2

A

2

B

3

B

3

B

3

 

that means I want to select a row those many times what is mentioned in product_sold column.

 

Thanks,

2 REPLIES
Junior Contributor

Re: Need help in SQL

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

 

Enthusiast

Re: Need help in SQL

Hi,

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,
 DEFAULT MERGEBLOCKRATIO
 (
  P_NAME VARCHAR(2),
 P_SOLD INTEGER
 )
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)
AS
(
SELECT P_NAME, P_SOLD AS P_N1 , 1 AS CNT FROM
TEMP1_WRK
UNION ALL
SELECT SRC.P_NAME,SRC.P_SOLD,CNT+1
FROM
TEMP1_WRK SRC
INNER JOIN
T1
ON
SRC.P_NAME=P_C1
AND
CNT<SRC.P_SOLD
)
SELECT P_C1,P_N1 FROM T1
ORDER BY 1;

 

Thanks,

Deepak Arora

DARORA