SQL Query Needed

1

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

2

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.

3

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

Sorry for the delayed reply

4 
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
5

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