Hi Guys,I have multiple CTE, I have already merged multiple row into one column.My concern is how to capture the partno multiple model while it has also the same partno with one model.I need to group by by partno.. please see my sample result.Your help is very much appreciated. Thanks.reate table #Sample(LFlag int, TranMonth datetime,model nvarchar(35),Itemid nvarchar(35),partno nvarchar(35),produceqty int,requireqty int,newpartqty int,reclaimqty int,refurbqty int)insert into #Sample values(3,'2012-09-01','INCREDIBLE','P300-1110-INCREDIBLE-U','HTC1040',6682,61,0.86,0,43)insert into #Sample values(3,'2012-09-01','INCRDBL2RED','P300-1110-INCRDBL2RED-U','HTC1040',2354,3083,59,0,2)insert into #Sample values(3,'2012-09-01','INCREDIBLE2','P300-1110-INCREDIBLE2-U','HTC1040',188,188,36,0,0)insert into #Sample values(2,'2012-08-01','INCREDIBLE','P300-1110-INCREDIBLE-U','HTC1040',6562,68,95,10,15)insert into #Sample values(2,'2012-08-01','INCRDBL2RED','P300-1110-INCRDBL2RED-U','HTC1040',2354,3083,59,2,25)insert into #Sample values(1,'2012-07-01','INCREDIBLE2','P300-1110-INCREDIBLE2-U','HTC1040',156,188,36,0,12)insert into #Sample values(1,'2012-07-01','INCREDIBLE2','P300-1110-INCREDIBLE2-U','HTC1045',186,189,46,3,32);With CTAS(select LFlag, TranMonth, partNo, stuff( ( select distinct ',' + model from #Sample where LFlag = t.LFlag for xml path( '' ) ) , 1, 1, '' ) as model, ISNULL(SUM(Produceqty),0) AS Produceqty, ISNULL(SUM(REQUIREQTY),0) AS REQUIREDQTY, ISNULL(SUM(REQUIREQTY),0)-ISNULL(SUM(NEWPARTQTY+RECLAIMQTY+REFURBQTY),0) as CORESQTY, ISNULL(SUM(RECLAIMQTY),0) AS RECLAIMQTY, ISNULL(SUM(NEWPARTQTY),0) AS NEWPARTSQTY, ISNULL(SUM(REFURBQTY),0) AS REFURBQTY, ISNULL(SUM(RECLAIMQTY+REFURBQTY),0) AS RECREPAIRQTYfrom #Sample tgroup by LFlag, TranMonth, partNo, model--order by partno)Select partno, model, SUM(Case When LFlag=3 THen Produceqty Else 0 End) As Produceqty1, SUM(Case When LFlag=2 THen Produceqty Else 0 End) As Produceqty2, SUM(Case When LFlag=1 THen Produceqty Else 0 End) As Produceqty3 From CTGroup by partno, Model--, TranMonthResultPArtno--------------------MODEL----------------ProduceQTY_1--RequiredQTY_1--CORESQTY_1--Produce_2--RequiredQty_2--CoredQTY_2---Produceqty_3 --RequiredQTY_3--CORESQTY_3HTC1040 -- INCRDBL2RED,INCREDIBLE,INCREDIBLE2------156----------188-----------140------- 8916----- ---3151---------2945----------9224-----------3332----------3192 HTC1045 ---INCREDIBLE2----------------------- -----186----------189------------108