Merge two Rows in single Row with new column name in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months ago

From my query i got output like :

Sr_NO Particulars IT_CODE UOM Opening_stock_Quantity

1 PPC TRADE YELLOW RED STRIP (ULTRATECH CUNNAR) MMI00A001569 PCS 1000.00

2 PPC TRADE YELLOW RED STRIP (ULTRATECH CUNNAR) MMI00A001569 PCS 35000.00

i want to merge this two rows in single row with new column name like i got Opening_stock_Quantity two different in above output but i want to get it in single row such as "Opening_stock_Quantity","Opening_stock_Other_Quantity" with "1000.00","35000.00" in sql.

Posted 3 months ago

Hi varun.p,

Refer below sample query.

SQL

DECLARE @tblQuantity AS TABLE(SR_No INT, Particulars NVARCHAR(MAX),IT_CODE NVARCHAR(MAX), UOM NVARCHAR(MAX),Opening_stock_Quantity INTEGER)
INSERT INTO @tblQuantity VALUES(1,'PPC TRADE YELLOW RED STRIP (ULTRATECH CUNNAR)','MMI00A001569','PCS',1000.00)
INSERT INTO @tblQuantity VALUES(2,'PPC TRADE YELLOW RED STRIP (ULTRATECH CUNNAR)','MMI00A001569','PCS',35000.00)
SELECT TOP 1 SR_No
	   ,Particulars
	   ,IT_CODE
	   ,UOM
	   ,Opening_stock_Quantity
	   ,(SELECT TOP 1 Opening_stock_Quantity FROM @tblQuantity ORDER BY SR_NO DESC) Opening_stock_Other_Quantity
FROM @tblQuantity

Output

SR_No Particulars IT_CODE UOM Opening_stock_Quantity Opening_stock_Other_Quantity
1 PPC TRADE YELLOW RED STRIP (ULTRATECH CUNNAR) MMI00A001569 PCS 1000 35000