Help: Join few records in a same field and same database table.

Hi guys,

I need urgent help here. How to join few records in a same field and same database table. For example:

id sequence_no itemcode description
1 1 APP Apple
1 2 BNN Banana
2 1 ORG Orange

What is the sql query that will return row as follow:

id description
1 Apple Banana
2 Orange

Thank you in advance guys...

hi, you can try this

-- create a function
CREATE FUNCTION dbo.GetFruitSequence
(
@.id int
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @.FruitSequence varchar(100)
SET @.FruitSequence = ''

SELECT @.FruitSequence = @.FruitSequence + [description] + ', '
FROM Fruits
WHERE [id] = @.id
ORDER BY
[sequence_no]

IF @.FruitSequence <> '' SET @.FruitSequence = LEFT(@.FruitSequence, LEN(@.FruitSequence) - 1)

RETURN @.FruitSequence

END

GO

-- to use the function
SELECT DISTINCT [id]
, dbo.GetFruitSequence([id]) as [description]
FROM Fruits
ORDER BY
[id]

GO|||Thank you Rhamille|||

You can use the following query in SQL Server 2005

Code Snippet

Create Table #itemdata (

[id] int ,

[sequence_no] int ,

[itemcode] Varchar(100) ,

[description] Varchar(100)

);

Insert Into #itemdata Values('1','1','APP','Apple');

Insert Into #itemdata Values('1','2','BNN','Banana');

Insert Into #itemdata Values('2','1','ORG','Orange');

Select

id

,(Select [description] + ' ' as [text()] From #itemdata as Sub Where Sub.id=Main.id For XML Path(''), ELEMENTS) as List

From

#itemdata as Main