how to combine result of a same column records

Question

select PRODUCTPRICEPLANID,PRICEPLANDESCRIPTION,OPERATORNAME,SOCCODE from 
                (SELECT  
                ppp.PRODUCTPRICEPLANID,
                ps.PRICEPLANDESCRIPTION,
                O.OPERATORNAME,
                osoc.SOCCODE
FROM PRODUCTPRICEPLAN ppp 
join PRICEPLANSUMMARY ps 
on ppp.PRODUCTPRICEPLANID=ps.PRODUCTPRICEPLANID_FK 
join PRICEPLANSOC psoc 
on ppp.PRODUCTPRICEPLANID=psoc.ProductPricePlanID_FK
join OPERATORSOC osoc 
on psoc.OPERATORSOCID_FK=osoc.OPERATORSOCID
join operators o on osoc.OPERATORID_FK=O.OPERATORID
where ppp.PRODUCTPRICEPLANID=95) t 
GROUP BY  PRODUCTPRICEPLANID,PRICEPLANDESCRIPTION,OPERATORNAME,SOCCODE

Currently I am getting this result set from my query:

PRODUCTPRICEPLANID  PRICEPLANDESCRIPTION    OPERATORNAME    SOCCODE
 95                    TMUS UnBundled        T-Mobile US     BRVC
 95                    TMUS UnBundled        T-Mobile US    INROAM

But i want this:

PRODUCTPRICEPLANID  PRICEPLANDESCRIPTION    OPERATORNAME    SOCCODE
 95                    TMUS UnBundled        T-Mobile US     BRVC,INROAM

possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Pரதீப், Feb 03 '15 at 06:56
This sort of question has been answered too many times here. Search SO or google for **simulating Group_concat** in `sql server ` — Pரதீப், Feb 03 '15 at 06:57

score 0 · Accepted Answer · answered Feb 03 '15 at 07:08

Write as:

;WITH CTE AS 
    (SELECT  
    ppp.PRODUCTPRICEPLANID,
    ps.PRICEPLANDESCRIPTION,
    O.OPERATORNAME,
    osoc.SOCCODE
    FROM PRODUCTPRICEPLAN ppp 
    join PRICEPLANSUMMARY ps 
    on ppp.PRODUCTPRICEPLANID=ps.PRODUCTPRICEPLANID_FK 
    join PRICEPLANSOC psoc 
    on ppp.PRODUCTPRICEPLANID=psoc.ProductPricePlanID_FK
    join OPERATORSOC osoc 
    on psoc.OPERATORSOCID_FK=osoc.OPERATORSOCID
    join operators o on osoc.OPERATORID_FK=O.OPERATORID
    where ppp.PRODUCTPRICEPLANID=95)
select PRODUCTPRICEPLANID,PRICEPLANDESCRIPTION,OPERATORNAME,
    STUFF((SELECT ', ' + CAST(SOCCODE AS VARCHAR(10)) [text()]
    FROM CTE AS CTE1
    WHERE CTE1.PRODUCTPRICEPLANID = t.PRODUCTPRICEPLANID 
    AND CTE1.PRICEPLANDESCRIPTION = t.PRICEPLANDESCRIPTION
    AND CTE1.OPERATORNAME = t.OPERATORNAME
    FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') AS SOCCODE 
from CTE t
GROUP BY  PRODUCTPRICEPLANID,PRICEPLANDESCRIPTION,OPERATORNAME

please accept the answer if it has helped you..:)..To mark an answer as accepted, click on the check mark beside the answer to toggle it from hollow to green.. — Deepshikha, Feb 03 '15 at 07:20

Onur Cete · Answer 2 · 2015-02-03T07:41:37.653

You can try;

select PRODUCTPRICEPLANID,
   PRICEPLANDESCRIPTION,
   OPERATORNAME,
   LISTAGG(SOCCODE, ',') WITHIN GROUP(ORDER BY SOCCODE) text
  from (select PRODUCTPRICEPLANID,
           PRICEPLANDESCRIPTION,
           OPERATORNAME,
           SOCCODE
          from (SELECT ppp.PRODUCTPRICEPLANID,
                   ps.PRICEPLANDESCRIPTION,
                   O.OPERATORNAME,
                   osoc.SOCCODE
                 FROM PRODUCTPRICEPLAN ppp
              join PRICEPLANSUMMARY ps
                on ppp.PRODUCTPRICEPLANID = ps.PRODUCTPRICEPLANID_FK
              join PRICEPLANSOC psoc
               on ppp.PRODUCTPRICEPLANID = psoc.ProductPricePlanID_FK
              join OPERATORSOC osoc
                on psoc.OPERATORSOCID_FK = osoc.OPERATORSOCID
              join operators o
               on osoc.OPERATORID_FK = O.OPERATORID
             where ppp.PRODUCTPRICEPLANID = 95) t
     GROUP BY PRODUCTPRICEPLANID,
              PRICEPLANDESCRIPTION,
             OPERATORNAME,
              SOCCODE)
 group by PRODUCTPRICEPLANID, PRICEPLANDESCRIPTION, OPERATORNAME

how to combine result of a same column records

2 Answers2