SQL Server : concatenating values

Question

The following SQL Server query need help

select 
    JI.id, RBIEnvironment
from
   (select 
       issue,
       case when cfname='RBI-Environment' 
              then customvalue 
             else null 
       end as 'RBIEnvironment' 
    from X) as CT
where 
    CT.issue = JI.id
group by 
    JI.id, RBIEnvironment

The issue is the CT table returns the data in following format

1   NULL
1   STG
1   PROD
2   SIT
2   DIT
3   SIT

Hence the outer query returns

1   NULL
1   STG
1   PROD
2   SIT
2   DIT
3   SIT

I need in following format

1   STG,PROD
2   SIT,DIT
3   SIT

Please use the same inner dynamic table as such as it contains some more columns which needs to be displaye din the resultselt .This inner table is linked with the outer table using the issue field and ID field respectively

score 2 · Accepted Answer · answered Jul 23 '13 at 08:33

2

Try STUFF() function for that:

SELECT distinct ID, RBIEnvironment = 
    STUFF((SELECT ' , ' + RBIEnvironment
           FROM MyTable b 
           WHERE b.ID = a.ID
          FOR XML PATH('')), 1, 2, '')
FROM MyTable a
GROUP BY ID;

Output:

| ID | RBIENVIRONMENT |
-----------------------
|  1 |     STG , PROD |
|  2 |      SIT , DIT |
|  3 |            SIT |

See this SQLFiddle

answered Jul 23 '13 at 08:33

Himanshu

31,810
31
111
133

2

thanks for pointing out SQLFiddle, it's just great – andreister Jul 23 '13 at 08:39
@hims056, I have modified your fiddle , as my slect table is a dynamic one and thus it fails. – Alfaromeo Jul 23 '13 at 10:12
@Alfaromeo - Can you show what you want exactly? – Himanshu Jul 23 '13 at 10:13
Thanks,but my requirement is that the inner dynamic table is required whereas here it is already created.If I use the alias ,invalid object error occurs. – Alfaromeo Jul 23 '13 at 11:07
@Alfaromeo - Can you show your code in [this SQLFiddle](http://sqlfiddle.com/#!3/7172c/2) – Himanshu Jul 23 '13 at 11:09
Any luck withe the code – Alfaromeo Jul 24 '13 at 06:50
@Alfaromeo - I can't understand what exactly you want to do :( Can you explain with some expected result? – Himanshu Jul 24 '13 at 06:53
Your code is good ,the only issue is that the inner table already exist.For me the table is an alias of the select query. – Alfaromeo Jul 24 '13 at 07:08

score 1 · Answer 2 · answered Jul 23 '13 at 08:40

You can use STUFF..FOR XML construct for concatenating column values.

WITH CT AS 
(
    select issue,
        case when cfname='RBI-Environment' 
                then customvalue 
             else null 
             end as 'RBIEnvironment' 
    from X
)
SELECT issue, 
STUFF((SELECT ', ' + b.RBIEnvironment 
       FROM CT b 
        WHERE a.issue = b.issue 
         FOR XML PATH('')),1,1,'')
FROM CT a
GROUP BY a.issue

SQLFiddle DEMO - simplified for CT CTE

SQL Server : concatenating values

2 Answers2

See this SQLFiddle