Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Merge mulitple rows into one column

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-10-19 : 04:02:55
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 CT
AS
(
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 RECREPAIRQTY
from #Sample t
group 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 CT
Group by partno, Model--, TranMonth




Result

PArtno--------------------MODEL----------------ProduceQTY_1--RequiredQTY_1--CORESQTY_1--Produce_2--RequiredQty_2--CoredQTY_2---Produceqty_3 --RequiredQTY_3--CORESQTY_3
HTC1040 -- INCRDBL2RED,INCREDIBLE,INCREDIBLE2------156----------188-----------140------- 8916----- ---3151---------2945----------9224-----------3332----------3192
HTC1045 ---INCREDIBLE2----------------------- -----186----------189------------108



Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-19 : 12:49:34
What do you want for a result, based on your sample data?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-19 : 12:58:09
Just a shot in the dark:
;With CT
AS
(
select
partNo,
stuff( ( select distinct ',' + model from #Sample where partNo = t.partNo 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 RECREPAIRQTY
from
#Sample t
group by
partNo
)
SELECT
CT.partno,
CT.model,
SUM(Case When S.LFlag=3 THen S.Produceqty Else 0 End) As Produceqty1,
SUM(Case When S.LFlag=2 THen S.Produceqty Else 0 End) As Produceqty2,
SUM(Case When S.LFlag=1 THen S.Produceqty Else 0 End) As Produceqty3
FROM CT
INNER JOIN
#Sample AS S
ON CT.PartNo = S.PartNo
Group by CT.partno, CT.Model
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-10-21 : 20:25:01
Thank you very much for your reply.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-22 : 12:13:54
see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -