VendorName	ItemCode	InvNo	Qty	Price	SerialNo
ABC		Item1		1001	50	120	4100/01
ABC		Item1		1001	50	120	4100/02
ABC		Item1		1001	50	120	4100/03
ABC		Item1		1001	50	120	4100/04

VendorName	ItemCode	InvNo	Qty	Price	SerialNo
ABC		Item1		1001	50	120	4100/01,4100/02,4100/03,4100/04

Means we need to concat all serial no in one row itself for a sinle group of data.

Please help me.

Regards,
abhi

DECLARE @temp TABLE ( VendorName VARCHAR(100), ItemCode	VARCHAR(100),InvNo	INT, Qty INT,Price	INT, SerialNo VARCHAR(100))
INSERT INTO @temp
SELECT  'ABC',		'Item1',		1001,	50,	120,	'4100/01'   UNION ALL
SELECT  'ABC',		'Item1',		1001,	50,	120,	'4100/02'   UNION ALL
SELECT  'ABC',		'Item1',		1001,	50,	120,    '4100/03'   UNION ALL
SELECT  'ABC',		'Item1',		1001,	50,	120,	'4100/04' 

SELECT DISTINCT
	VendorName,
	ItemCode,
	InvNo,
	Qty,
	Price,
	STUFF(( SELECT DISTINCT ','+SerialNo 
			FROM  @temp 
			WHERE VendorName = T.VendorName
			AND ItemCode = T.ItemCode
			AND InvNo = T.InvNo
			AND Qty = T.Qty
			AND Price = T.Price
			FOR XML PATH('')),1,1,'') AS SerialNo
FROM @Temp T

quote:

---

Originally posted by abhit_kumar

Thanks Raky and Madhivan

---

quote:

---

Originally posted by abhit_kumar

hello raky,

can u please tell me why we have used

FOR XML PATH('')),1,1,''

---