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

Why don't you show what data looks like in the tables you are pulling data out of so that we can think of how to reach the conclusion that you have described? — Ahmad, Aug 14 '14 at 05:22
**First tip**: turn **OFF** your CAPS LOCK! Writing in ALL CAPS is annoying, it's really hard to read, it's considered **SHOUTING** at people - those people who might be able to help you. It's rude - just **don't do it!** — marc_s, Aug 14 '14 at 05:25
sorry i am new here i dont the does and dont thank you for your concenrn — newb, Aug 14 '14 at 05:29

score 2 · Answer 1 · edited May 23 '17 at 12:31

In SQL Server, the type of comma separated output is achieved with "for xml path", but is also usually associated with stuff()

Optionally via "cross apply" although it doesn't have to be done that way.

I would approach it like this. Don't use pivot now, just reduce MDCODE & ACT_DESC by distinct or group by, then using cross apply concatenate the string of "dates". If they really are dates then you will need to convert them to varchar e.g. convert(varchar,actual_date,112)

| MDCODE | ACT_DESC |  ACTUAL_DATES |
|--------|----------|---------------|
|   1234 |  sample1 |        1/2014 |
|   1234 |  sample2 | 2/2014,3/2014 |

produced by:

SELECT
      d.mdcode
    , d.act_desc
    , ca1.actual_dates
FROM (
            SELECT DISTINCT
                  [mdcode]
                , [act_desc]
            FROM Table1
      ) d
      CROSS APPLY (
                  SELECT
                        STUFF((
                              SELECT
                                    ',' + a.actual_date
                              FROM table1 a
                              WHERE a.mdcode = d.mdcode
                                  AND a.act_desc = d.act_desc --<< change here
                              ORDER BY a.actual_date
                              FOR xml PATH ('')
                        )
                        , 1, 1, '')
            ) AS ca1 (actual_dates)
;

By the way STUFF() is used to remove the first comma from the concatenation, that is its only role. CROSS APPLY is arguably better than using a correlated subquery in the select clause, because it is performed before that clause as part of the from clause. As I said earlier its optional but I prefer it.

see: http://sqlfiddle.com/#!3/a24ba/4 & also see these samples

you don't need the pivot at all, it will just complicate things unnecessarily, please give me some time. — Paul Maxwell, Aug 14 '14 at 05:55
Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'convert'. a.convert(varchar,actual_date,112) its error when i convert it — newb, Aug 14 '14 at 06:10
convert(varchar,a.actual_date,112) don't put the alias at the front of convert, the alias attaches to the field name — Paul Maxwell, Aug 14 '14 at 06:19
why is data added the output should be '>| MDCODE | ACT_DESC | ACTUAL_DATES |' '>|--------|----------|----------------------|' '>| 1234 | sample1 | 1/2014 |' '>| 1234 | sample2 | 1/2014,2/2014,3/2014 |' — newb, Aug 14 '14 at 06:54
the query may need one extra line look for `--<< change here` in the query above — Paul Maxwell, Aug 14 '14 at 08:20

score 1 · Accepted Answer · answered Aug 14 '14 at 06:06

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

Concatenate columns in Pivot View

2 Answers2