SQLTeam.com | Weblogs | Forums

SQL Query Needed

Hi,
I'm using SQL Server 2005 and my requirement is to use a pivot command and get the necessary output.

Data
Create Table _Test (Branch varchar(30),Segment char(1),Mth smallint,Yrs smallint,FY varchar(10),Qty float,Ret float)

Insert Into _Test
Select 'CHN','A',4,2019,'FY-1920',49.00,11.35
Union All
Select 'CHN','A',5,2019,'FY-1920',50.00,13.30
Union All
Select 'CHN','B',4,2019,'FY-1920',7.00,1.70
Union All
select 'CHN','B',5,2019,'FY-1920',19.00,4.15
Union All
Select 'CHN','C',4,2019,'FY-1920',54.00,8.52
Union All
Select 'CHN','C',5,2019,'FY-1920',33.00,5.30
Union All
Select 'CHN','D',4,2019,'FY-1920',113.00,10.80
Union All
Select 'CHN','D',5,2019,'FY-1920',92.00,8.82
Union All
Select 'CUD','A',4,2019,'FY-1920',11.00,3.30
Union All
Select 'CUD','A',5,2019,'FY-1920',13.00,3.90
Union All
Select 'CUD','B',4,2019,'FY-1920',8.00,2.00
Union All
Select 'CUD','B',5,2019,'FY-1920',3.00,0.75
Union All
Select 'CUD','C',4,2019,'FY-1920',9.00,4.70
Union All
Select 'CUD','C',5,2019,'FY-1920',13.00,5.07
Union All
Select 'CUD','D',4,2019,'FY-1920',81.00,13.02
Union All
Select 'CUD','D',5,2019,'FY-1920',80.00,11.95

Output
Branch Mth A Qty A Ret B Qty B Ret C Qty C Ret D Qty D Ret
CHN 4 49 11.35 7 1.70 54 8.52 113 10.80
CHN 5 50 13.30 19 4.15 33 5.30 92 8.82
CUD 4 11 3.30 8 2.00 9 4.70 81 13.02
CUD 5 13 3.90 3 0.75 13 5.07 80 11.95

Thanks in advance

Nirene

A cross-tab query will handle this quite nicely. Is that acceptable or are you insisting on a PIVOT in the query? I'll write the cross-tab but, me personally, I'm not gonna mess with trying to PIVOT this.

Hi Scott,
My requirement says to use PIVOT can you help.

Sorry for the delayed reply

if object_id('tempdb..#_Test') is not null drop table #_Test 

Create Table #_Test (Branch varchar(30),Segment char(1),Mth smallint,Yrs smallint,FY varchar(10),Qty float,Ret float)

Insert Into #_Test
Select 'CHN','A',4,2019,'FY-1920',49.00,11.35
Union All
Select 'CHN','A',5,2019,'FY-1920',50.00,13.30
Union All
Select 'CHN','B',4,2019,'FY-1920',7.00,1.70
Union All
select 'CHN','B',5,2019,'FY-1920',19.00,4.15
Union All
Select 'CHN','C',4,2019,'FY-1920',54.00,8.52
Union All
Select 'CHN','C',5,2019,'FY-1920',33.00,5.30
Union All
Select 'CHN','D',4,2019,'FY-1920',113.00,10.80
Union All
Select 'CHN','D',5,2019,'FY-1920',92.00,8.82
Union All
Select 'CUD','A',4,2019,'FY-1920',11.00,3.30
Union All
Select 'CUD','A',5,2019,'FY-1920',13.00,3.90
Union All
Select 'CUD','B',4,2019,'FY-1920',8.00,2.00
Union All
Select 'CUD','B',5,2019,'FY-1920',3.00,0.75
Union All
Select 'CUD','C',4,2019,'FY-1920',9.00,4.70
Union All
Select 'CUD','C',5,2019,'FY-1920',13.00,5.07
Union All
Select 'CUD','D',4,2019,'FY-1920',81.00,13.02
Union All
Select 'CUD','D',5,2019,'FY-1920',80.00,11.95



select
 a.Branch	
,a.Mth	
,a.Yrs	
,a.FY	
,b.A as [A Qty]	
,a.A as [A Ret]	
,b.B as [B Qty]		
,a.B as [B Ret]	
,b.C as [C Qty]		
,a.C as [C Ret]	
,b.D as [D Qty]	
,a.D as [D Ret]	
from (
		select * from (
		select 
		 Branch	
		,Segment	
		,Mth	
		,Yrs	
		,FY
		,Ret
		from #_Test
		)Ret pivot (sum(Ret) for Segment in ([A],[B],[C],[D]))pt
)A
Left join (
			select * from (
			select 
			 Branch	
			,Segment	
			,Mth	
			,Yrs	
			,FY
			,Qty
			from #_Test
			)_Qty pivot (sum(Qty) for Segment in ([A],[B],[C],[D]))pt
) B on A.Branch = b.Branch and a.Mth = b.Mth and a.FY = b.FY and a.Yrs = b.Yrs

what about this way:- ???

select * from (
select * from #_Test unpivot ( xx for rr in ([Qty],[Ret]))unpvt
)main pivot (sum(xx) for Segment in ([A],[B],[C],[D]))pt