SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Merge mulitple rows into one column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 10/19/2012 :  04:02:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

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

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/19/2012 :  12:58:09  Show Profile  Reply with Quote
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

441 Posts

Posted - 10/21/2012 :  20:25:01  Show Profile  Reply with Quote
Thank you very much for your reply.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/22/2012 :  12:13:54  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000