Select Query to split single Row to Multiple Rows based on a Column Value

[#1]

hi Team,

I have an invoice Table, with feilds as shown below.

Now , i have to write a select query in such a way that we should obtain additional line item if value exist in UNIT_PRICE,FREIGHT_CHRG,INSURACE_CHRG& MAINT_CHRG . i.e. the first Invoice INV100 should be shown into 4 Line Items and second invoice will be shown as 2 line items as shown below in the second snap shot.

line Item 1 represents Unit Price of INV100, with value 1000$ in RESULTANT_UNIT_PRICE Column

Line Item 2 represents FREIGHT_CHRG of INV100, with value of 100$ in RESULTANT_UNIT_PRICE Column

Line Item 3 represents INSURANCE_CHRG of INV100, with value of 10$ in RESULTANT_UNIT_PRICE Column

Line Item 4 represents MAINT_CHRG of INV100,with value of 5$ in RESULTANT_UNIT_PRICE Column

Line Item 5 represents UNIT_PRICE of INV200, with value of 2000$ in RESULTANT_UNIT_PRICE Column

Line Item 6 represents FREIGHT_CHRG of INV200 with value of 100$ in RESULTANT_UNIT_PRICE Column .

INV200 has only two line Items because it has only unit price and frieght chrg , and zero insurace & maint charges so it has no line items representing those two.

how to write a select query for the same,

Kindly do the needful.

Regards

Govardan

[KonradZaleski · Accepted solution]

Simply use union to pivot the table. Something like this:

SELECT 
	INV_NO,
	INV_VALUE,
	QTY,
	RESULTANT_UNIT_PRICE,
	TAX_A_AMT,
	TOTAL
FROM
(
	SELECT 
		INV_NO,
		INV_VALUE,
		QTY,
		UNIT_PRICE AS RESULTANT_UNIT_PRICE,
		UNIT_PRICE / TAX_A_PRCNT AS TAX_A_AMT,
		UNIT_PRICE + UNIT_PRICE / TAX_A_PRCNT AS TOTAL
	FROM 
		TABLE


	UNION


	SELECT 
		INV_NO,
		INV_VALUE,
		QTY,
		FREIGHT_CHRG AS RESULTANT_UNIT_PRICE,
		FREIGHT_CHRG / TAX_A_PRCNT AS TAX_A_AMT,
		FREIGHT_CHRG + FREIGHT_CHRG / TAX_A_PRCNT AS TOTAL
	FROM 
		TABLE


	UNION


	SELECT 
		INV_NO,
		INV_VALUE,
		QTY,
		INSURANCE_CHRG AS RESULTANT_UNIT_PRICE,
		INSURANCE_CHRG / TAX_A_PRCNT AS TAX_A_AMT,
		INSURANCE_CHRG + INSURANCE_CHRG / TAX_A_PRCNT AS TOTAL
	FROM 
		TABLE


	UNION


	SELECT 
		INV_NO,
		INV_VALUE,
		QTY,
		MAINT_CHRG AS RESULTANT_UNIT_PRICE,
		MAINT_CHRG / TAX_A_PRCNT AS TAX_A_AMT,
		MAINT_CHRG + MAINT_CHRG / TAX_A_PRCNT AS TOTAL
	FROM 
		TABLE
)
;

[govardan_raj · Accepted solution]

hi konrad

Am able to get the output for above columns , but when am trying to get additional column in this it is throwing 400 BAD Request , as shown below.

kindly suggest how to optimise the query.

Regards

Govardan

[KonradZaleski · Accepted solution]

I don't think that this is performance issue.