Concatinating Rows Specific Field and Displaying the Concatinated Value in SELECT query-SQL

Question

My aim is to display the person name and all the reason for his absences(concated and displaying in a string).

I am using the following query to display the EmployeeName, ReasonForAbsence. I am having problem with concatenating and Displaying all record specific column called 'AbsenceReason'. The error is Incorrect Syntax near ='. More info about error- its happening for displaying absence reason part.

SELECT 
             --Displaying Name,             
              EMP.NAME,


             --Displaying Absence Reason
             (
               SELECT
                    @AbsenceReasons= @AbsenceReasons + ';' + REASONTEXT 
               FROM 
                    ABSENCE
                 WHERE ID=EMP.ID
               )  
FROM
           (
             SELECT
                    * 
              FROM 
                      Employees
            ) EMP

What have I missed?

Thank you

score 1 · Accepted Answer · answered Sep 19 '11 at 13:57

As Martin says this is a duplicated question but...

SELECT DISTINCT Absence.EmpId, Reasons.AllReasons
      FROM Absence 
     CROSS APPLY ( SELECT ReasonText + ' ,' 
                    FROM Absence EMP2
                    WHERE EMP2.EmpId= Absence.EmpId
                    FOR XML PATH('') 
)  Reasons ( AllReasons)

Concatinating Rows Specific Field and Displaying the Concatinated Value in SELECT query-SQL

1 Answers1