Concatenating strings in MS SQL

[Jet]

Hello, i need help for a stored procedure that allows me to concat string fields from a table column when they have the same field names. i did it when the fields were int and it worked perfectly code snippet.

SELECT X.FieldName,SUM(CAST(Y.Value AS float))
FROM L INNER JOIN
C ON L.Id = C.Id INNER JOIN
B ON L.Id = B.LocalityId INNER JOIN
FormInstances ON B.FormInstanceId = FormInstances.Id INNER JOIN
Y ON FormInstances.Id = Y.FormInstanceId INNER JOIN
X ON Y.FieldId = X.Id
WHERE (C.AId = @aid) AND (FormInstances.FormId = 6)
GROUP BY X.FieldName

this got me the sum of each field in Y based on X.fieldname. now i want to do the same when the values in Y are not texts and not numbers. i tried conctenation(+) but was not successful with. thanks in advance.

[Corporal Agarn]

I do not understand what you want. However keep in mind that if you concatenate a null value the result will be null. 'John' + NULL + 'Doe' = NULL Try using the ISNULL function. Also you cannot mix types 'John' + 1.0 will give an error. You need to make all compatible types. Good Luck djj

[Jet]

Thanks so much for the reply. 1.all the data types are verified to be strings(text) 2. i want to concatenate the strings by grouping them on a fieldid. when i pick an id i want to concat all values corresponding to the id. (this is the structure. i have table A which contains list of towns. table B contains list of regions. table C contains list of attributes of the towns table D contains the values of the attributes the task is to provide a summary for towns under a region. grouped under the various attributes. for Region A i have Attribute A: then the concatenated values for all attributes A for each town. thanks once again.

[Corporal Agarn]

Do you really want to concatenate columns or just create a table?

-- Table
SELECT A.Town, B.Region, C.Attribute AS AttribCode, D.Attribute
FROM TableA A
INNER JOIN TableB B
ON A.ID = B.ID
INNER JOIN TableC C ........

--or
-- Concatenate
SELECT A.Town + ISNULL(B.Region, '') + ISNULL(C.Attribute, '') + ISNULL(D.Attribute, '') AS TownInfo
FROM TableA A
INNER JOIN TableB B
ON A.ID = B.ID
INNER JOIN TableC C ........

If you have a one to many relationship and want the say multiple attributes for a given town you will need to Google that as it has been covered in various places. I hope this helps, djj