String concatenation in Dynamic SQL using PIVOT table

Question

I have to work on a mapping from an ERP system to a MySQL database. The structure that is present in the ERP system is:

 _____________________________________
| Article | Feature       | Criterion |
|---------|---------------|-----------|
| Art1    | size          | 4*10      |
| Art1    | color         | red       |
| Art1    | functionality | doesA     |
| Art1    | ...           | ...       |
| Art2    | size          | 2*5       |
| Art2    | color         | green     |
| Art2    | functionality | doesB     |
| Art2    | ...           | ...       |
 -------------------------------------

What i need to do is map it like this:

 ________________________________________________
| Article | size | color | functionality | ...   |
|---------|------|-------|---------------|-------|
| Art1    | 4*10 | red   | doesA         | ...   |
| Art2    | 2*5  | green | doesB         | ...   |
 ------------------------------------------------

I can access the ERP system via T-SQL and can perform a working dynamic query, that provides me a table and looks like:

DECLARE @cols AS nvarchar(MAX),
@query  AS nvarchar(MAX)


SELECT @cols =  stuff((SELECT DISTINCT ', ' + quotename(f.Feature) + ''
FROM CRITERION c, FEATURE f
WHERE  --necessary joins

FOR xml PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')


SET @query = N'SELECT Article, ' + @cols + N' 
    FROM (
    SELECT Article, Feature, Criterion
    FROM --necessary tables
    WHERE --necessary joins
    ) x

    pivot 
    (
        max(Criterion)
        FOR Feature IN (' + @cols + N')
    ) p                
    '

EXEC sp_executesql @query;

The problem that is coming up now is, that the system features multiple selection for some of the features:

 _____________________________________
| Article | Feature       | Criterion |
|---------|---------------|-----------|
| Art3    | color         | red       |
| Art3    | color         | green     |
 -------------------------------------

and the query just gives me the first result in the table.

 ________________________________________
| Article | size | color | functionality |
|---------|------|-------|---------------|
| Art3    | ...  | red   | ...           |
 ----------------------------------------

So my question is, if there is any way to add a string concatenation either in the subquery 'x' or in the pivot table 'p', so the result becomes following:

 _____________________________________________
| Article | size | color      | functionality |
|---------|------|------------|---------------|
| Art3    | ...  | red, green | ...           |
 ---------------------------------------------

score 0 · Answer 1 · answered Aug 03 '16 at 12:18

GROUP BY features first using the same FOR XML trick. Kind of

SET @query = N'SELECT Article, ' + @cols + N' 
    FROM (
      SELECT Article,  Criterion, 
      Feature = stuff(
        (SELECT '',''+ t2.Feature 
         FROM ttt as t2 
         WHERE t2.Article = t1.Article AND
            t2.Criterion = t1.Criterion
         FOR XML PATH(''))
       ,1,1,'''')
     FROM ttt t1
     GROUP BY Article,  Criterion    
    ) x

    pivot 
    (
        max(Criterion)
        FOR Feature IN (' + @cols + N')
    ) p                
    '

Replace ttt with real data sources.

score 0 · Accepted Answer · answered Aug 03 '16 at 14:40

@Serg has the right idea but the fields seem to be off. This should be closer.

SET @query = N'
    SELECT Article, ' + @cols + N' 
    FROM (
            SELECT  Article, 
                    Feature,
                    Criterion = STUFF(
                                        (SELECT '', '' + t2.Criterion 
                                         FROM   t1 as t2 
                                         WHERE  t2.Article = t1.Article 
                                                AND t2.[Feature] = t1.[Feature]
                                         FOR XML PATH('''')), 1, 2,'''')
            FROM (SELECT    Article, Feature, Criterion
                  FROM      --necessary tables
                  WHERE     --necessary joins) t1
        ) x
    pivot 
    (
        MAX(Criterion)
        FOR Feature IN (' + @cols + N')
    ) p                
    '

String concatenation in Dynamic SQL using PIVOT table

2 Answers2