How to pivot one column to show the data in one cell?

Question

I have my data in this form(multiple AUTHCD for the same person)

PERSNBR AUTHCD  VALUE   File#   Payroll Name    Reports To File#    Reports To Name
3533    CSR2    5874    109605  Burnette, Peter M   105874  Vo, Timothy
3533    DOPS    5874    109605  Burnette, Peter M   105874  Vo, Timothy
3533    IT03    5874    109605  Burnette, Peter M   105874  Vo, Timothy
3533    LSUP    5874    109605  Burnette, Peter M   105874  Vo, Timothy
3533    OSUP    5874    109605  Burnette, Peter M   105874  Vo, Timothy

But I want my data to show like this:

PERSNBR AUTHCD  VALUE   File#   Payroll Name    Reports To File#    Reports To Name
3533    CSR2, DOPS, IT03, LSUP  5874    109605  Burnette, Peter M   105874  Vo, Timothy

The reason I want the column AUTHCD to show like this is because when I use the sample data and create SSRS report, it just show the first AUTHCD and chop off the rest whereas I want all the auth codes for that employee. Should i be doing it in SSRS or sql code? Here is my code:

   select  pa.PERSNBR
            ,pa.AUTHCD
            ,pu.VALUE
            ,File#, [Payroll Name]
    ,       [Reports To File#], [Reports To Name]
    ,      [EMC #], [EMC Name]
    ,       1 as level
    from    
                            [DNA_Staging].[dbo].[PERSAUTH] pa
            join    [DNA_Staging].[dbo].[PERSEMPL] pe
                on  pa.PERSNBR = pe.PERSNBR
            join    [DNA_Staging].[dbo].[PERSUSERFIELD] pu
                on  pe.PERSNBR = pu.PERSNBR
                and pu.USERFIELDCD = 'EFNR'
                and GETDATE() < isnull(pe.inactivedate,getdate()+1)

     join       [HR_Staging].[dbo].[HR_EmployeeHierarchyStaging] emp

    on  pu.VALUE = substring(emp.[Reports To File#],2,6)
                or  pu.VALUE = substring(emp.[Reports To File#],3,6)

    where [Reports To File#] = @ReportToFile

Thanks in advance!

Answer 1

Below Stack overflow link has the similar question with answer. With CROSS APPLY you need to first concatinate your columns with comma seperated and then use PIVOT.

Stack Overflow Link