Concatenate columns in Pivot View

Question

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(ACT_DESC)
FROM (SELECT DISTINCT ACT_DESC FROM tpc) AS desc

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT MDCODE, ' + @ColumnName + '
    FROM tpc
    PIVOT(MAX(ACTUAL_DATE) 
          FOR tpcIN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

This query displays:

MDCODE | sample1 | sample2
--------------------------
123    | 1/2014  | 
123    |         | 2/2014
123    |         | 3/2014

What I want is this:

MDCODE | sample1 | sample2
--------------------------
123    | 1/2014  | 2/2014,3/2014

Does anyone have an idea how to concat the column data? Any tips?

This is the table where i get the data:

   mdcode | act_desc | actual_date
   --------------------------
   1234   | sample1  | 1/2014
   1234   | sample2  | 2/2014
   1234   | sample2  | 3/2014

the actual_date is datetime

Answer 1

I would skip the PIVOT altogether and just use FOR XML_PATH, like this:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @DynamicPivotQuery= ISNULL(@DynamicPivotQuery + ',','select mdcode,') 
       + 'stuff((select '',''+actual_date from tpc where mdcode=t.mdcode and act_desc = ''' + ACT_DESC + ''' for xml path(''''),type).value(''.'',''varchar(max)''),1,1,'''') '
       + QUOTENAME(ACT_DESC)
FROM (SELECT DISTINCT ACT_DESC FROM tpc) AS des

select @DynamicPivotQuery = @DynamicPivotQuery + 'from tpc t group by mdcode'
EXEC sp_executesql @DynamicPivotQuery

The dynamic query generates a query like this:

select mdcode,
        stuff(
              (
                select ','+actual_date
                from tpc where mdcode=t.mdcode and act_desc = 'sample1'
                for xml path(''),type
              ).value('.','varchar(max)')
              ,1,1,'') sample1,
        stuff(
              (
                select ','+actual_date
                from tpc where mdcode=t.mdcode and act_desc = 'sample2'
                for xml path(''),type
              ).value('.','varchar(max)')
              ,1,1,'') sample2
from tpc t
group by mdcode;

The SQL Fiddle demonstrates the static query and the dynamic one