Append Fields in SQL

[visweswaran2830]

Hi,



Consider, that I have 5 fields, In which first four fields has same value and fifth one has different value. If I retrieve these records from table it should shows first 4 fields only once and fifth fields should be appended. How can I achieve.

Eg:

field #1 field #2 field #3 field #4 field #5

101 VV BLR HOME 12K

101 VV BLR HOME 15K

101 VV BLR HOME 18K



if I retrieve means, It should come like.



101 VV BLR HOME 12K,15k,18k instead repeating all fields.

[gpl]

With the help of a function, this is quite easy; there is no generic solution, so it will have to be rewritten for every different situation.

CREATE FUNCTION dbo.AllField5 (@Field1 Varchar(255), @Field2 Varchar(255), @Field3 Varchar(255), @Field4 Varchar(255))
RETURNS Varchar(1024)

AS
BEGIN

DECLARE @Fld5List varchar(1024)

SELECT @Fld5List = COALESCE(@Fld5List + ', ', '') + CAST(MyTable.Field5 AS varchar(255))
FROM MyTable
WHERE 
      MyTable.Field1 = @Field1 AND
      MyTable.Field2 = @Field2 AND
      MyTable.Field3 = @Field3 AND
      MyTable.Field4 = @Field4 

	RETURN @Fld5List
END

And to use it

SELECT DISTINCT
      MyTable.Field1,
      MyTable.Field2,
      MyTable.Field3,
      MyTable.Field4,
      dbo.AllField5 (
                      MyTable.Field1,
                      MyTable.Field2,
                      MyTable.Field3,
                      MyTable.Field4
      ) as All5
FROM MyTable