required help for sql query

kalyan_vb

I have an problem in sequel query. i.e I have 2 table where master child relationship exits Table1 CorrespondentCodePK, ContactCode,PartNo, the data in Table1 is 1,1,1 2,1,1 3,1,2 Table2 CorrespondentCodeFK,PartNo,PartName, the data in Table2 is 1,1,Part1 1,1,Part2 2,1,Part1 and I want the result in the bellow mentioned format i.e CorrespondentCodePK,ContactCode,PartName (i.e all part names for the CorrespondentCodePK should come in PartName column as a one record) 1,1,Part1Part2 2,1,Part1

Eric Dahlvang

First, make a function: NOTE: The lengths of the varchar variables will depend upon your data.

CREATE FUNCTION GetPartNames (@nCorrCode int)  
RETURNS  varchar(1000) AS  
BEGIN 

DECLARE @cPartNames varchar(1000)
DECLARE @cPartName varchar(50)

SELECT @cPartNames = ''

DECLARE  partcursor CURSOR FOR 
SELECT DISTINCT PartName 
FROM table2
WHERE correspondentcodefk = @nCorrCode

OPEN partcursor
FETCH NEXT FROM partcursor INTO @cPartName 
WHILE @@FETCH_STATUS = 0
BEGIN
	select @cPartNames = @cPartNames + @cPartName
	FETCH NEXT FROM partcursor INTO @cPartName 
END

CLOSE partcursor
DEALLOCATE partcursor
RETURN(@cPartNames)
END

Next, use this SQL:

SELECT correspondentcodepk, contactcode, dbo.GetPartNames(correspondentcodepk) AS PartNames FROM table1

---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

r stropek

If you have SQL 2005 you could also write a short user-defined aggregation function in C# that concats strings. Regard, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers