Combining Rows after pivot - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Combining Rows after pivot

Author

Topic

Stevan2020
Starting Member

25 Posts

Posted - 2010-08-18 : 14:13:29

I have used this code to pivot data...

SELECT DISTINCT MpooID, MpooTitle, OfficeCount,
coalesce([754], 0) AS a754, coalesce([755], 0) AS a755,
coalesce([764], 0) AS a764, coalesce([767], 0) AS a767
FROM ( SELECT M.MpooID, M.MpooTitle, Count(I.FinTen) AS OfficeCount, I.AIC, Sum(I.Amount) AS Amount
FROM dbo.tbl_UEI_OpenEmployeeIRT_Mo AS I
LEFT JOIN tbl_Offices AS O ON I.FinTen = O.FinTen
LEFT JOIN tbl_MPOO M ON O.MpooID = M.MpooID
WHERE ( I.AIC IN ( '754', '755', '764', '767' ) ) AND
I.FinTen IN ( SELECT O.FinTen
FROM tbl_Offices O
LEFT JOIN tbl_MPOO M ON O.MpooID = M.MpooID
WHERE O.PFC = 970 )
GROUP BY M.MpooID, M.MpooTitle, I.AIC ) AS p
PIVOT ( Sum(Amount)
FOR [AIC]
IN ([754], [755], [764], [767]) ) AS pvt

The result is...
MpooID MpooTitle OfficeCount a754 a755 a764 a767
20 MPOO Area 1 1 555.01 0.00 0.00 0.00
22 MPOO Area 2 1 0.00 0.00 0.00 341.00
35 MPOO Area 3 1 0.00 0.00 0.00 1868.25
35 MPOO Area 3 2 0.00 1431.50 0.00 0.00
58 MPOO Area 5 1 0.00 0.00 0.00 87.83

How can I combine the rows that have the same MpooID into a single row?

Note when the value O.PFC = 970 is a variable that changes the results. The MpooID's returned will vary depending on the PFC.

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 14:44:43

as long as the pivot columns are always the same, then you can put the pivot in a subquery like this.


declare @te table (FinTen int, AIC varchar(10), Amount decimal(8,2),
					MpooID int)
declare @tm table (MpooID int, MPooTitle varchar(20))
declare @to table(pfc int, FinTen int, MpooID int)

insert into @te
select 1, '754', 100.00, 1 union all
select 1, '755', 22.22, 1 union all
select 3, '754', 333, 3 union all
select 1, '757', 44, 1 union all
select 1, '754', 55.55, 1
					
insert into @tm
select 1, 'Title 1' union all
select 2, 'Title 2' union all
select 3, 'Title 3'

insert into @to
select 970, 1, 1 union all
select 971, 2, 2 union all
select 970, 3, 3 union all
select 970, 2, 2

select MpooID, MpooTitle, SUM(OfficeCount) OfficeCount, 
		SUM(a754) a754, SUM(a755) a755, 
		SUM(a764) a764, SUM(a767) a767
from (		
SELECT  MpooID, MpooTitle, OfficeCount, 
coalesce([754], 0) AS a754, coalesce([755], 0) AS a755,
coalesce([764], 0) AS a764, coalesce([767], 0) AS a767 
FROM ( SELECT M.MpooID, M.MpooTitle, Count(I.FinTen) AS OfficeCount, I.AIC, Sum(I.Amount) AS Amount
FROM @te AS I 
LEFT JOIN @to AS O ON I.FinTen = O.FinTen 
LEFT JOIN @tm M ON O.MpooID = M.MpooID 
WHERE ( I.AIC IN ( '754', '755', '764', '767' ) ) AND 
I.FinTen IN ( SELECT O.FinTen 
FROM @to O 
LEFT JOIN @tm M ON O.MpooID = M.MpooID 
WHERE O.PFC = 970 ) 
GROUP BY M.MpooID, M.MpooTitle, I.AIC ) AS p 
PIVOT ( Sum(Amount) 
FOR [AIC] 
IN ([754], [755], [764], [767]) ) AS pvt ) v
group by MpooID, MpooTitle

Stevan2020
Starting Member

25 Posts

Posted - 2010-08-18 : 15:23:51

Thanks, that works great!

Here is my code, now...
Just curious, what is the significance of the table names @te, @tm and @to?
Is there a way to avoid using what is basically the same SELECT statement multiple times?

declare @te table (FinTen Char(10), AIC varchar(10), Amount decimal(8,2), MpooID int)
declare @tm table (MpooID int, MPooTitle varchar(20))
declare @to table(pfc int, FinTen Char(10), MpooID int)

insert into @te
SELECT I.FinTen, I.AIC, I.Amount, M.MpooID
FROM dbo.tbl_UEI_OpenEmployeeIRT_Mo AS I
LEFT JOIN tbl_Offices AS O ON I.FinTen = O.FinTen
LEFT JOIN tbl_MPOO M ON O.MpooID = M.MpooID
WHERE ( I.AIC IN ( '754', '755', '764', '767' ) ) AND
I.FinTen IN ( SELECT O.FinTen
FROM tbl_Offices O
LEFT JOIN tbl_MPOO M ON O.MpooID = M.MpooID
WHERE O.PFC = 970 )

insert into @tm
SELECT M.MpooID, M.MpooTitle
FROM dbo.tbl_UEI_OpenEmployeeIRT_Mo AS I
LEFT JOIN tbl_Offices AS O ON I.FinTen = O.FinTen
LEFT JOIN tbl_MPOO M ON O.MpooID = M.MpooID
WHERE ( I.AIC IN ( '754', '755', '764', '767' ) ) AND
I.FinTen IN ( SELECT O.FinTen
FROM tbl_Offices O
LEFT JOIN tbl_MPOO M ON O.MpooID = M.MpooID
WHERE O.PFC = 970 )

insert into @to
SELECT O.PFC, I.FinTen, M.MpooID
FROM dbo.tbl_UEI_OpenEmployeeIRT_Mo AS I
LEFT JOIN tbl_Offices AS O ON I.FinTen = O.FinTen
LEFT JOIN tbl_MPOO M ON O.MpooID = M.MpooID
WHERE ( I.AIC IN ( '754', '755', '764', '767' ) ) AND
I.FinTen IN ( SELECT O.FinTen
FROM tbl_Offices O
LEFT JOIN tbl_MPOO M ON O.MpooID = M.MpooID
WHERE O.PFC = 970 )

select MpooID, MpooTitle, SUM(OfficeCount) OfficeCount,
SUM(a754) a754, SUM(a755) a755,
SUM(a764) a764, SUM(a767) a767
from (
SELECT MpooID, MpooTitle, OfficeCount,
coalesce([754], 0) AS a754, coalesce([755], 0) AS a755,
coalesce([764], 0) AS a764, coalesce([767], 0) AS a767
FROM ( SELECT M.MpooID, M.MpooTitle, Count(I.FinTen) AS OfficeCount, I.AIC, Sum(I.Amount) AS Amount
FROM @te AS I
LEFT JOIN @to AS O ON I.FinTen = O.FinTen
LEFT JOIN @tm M ON O.MpooID = M.MpooID
WHERE ( I.AIC IN ( '754', '755', '764', '767' ) ) AND
I.FinTen IN ( SELECT O.FinTen
FROM @to O
LEFT JOIN @tm M ON O.MpooID = M.MpooID
WHERE O.PFC = 970 )
GROUP BY M.MpooID, M.MpooTitle, I.AIC ) AS p
PIVOT ( Sum(Amount)
FOR [AIC]
IN ([754], [755], [764], [767]) ) AS pvt ) v
group by MpooID, MpooTitle

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-19 : 08:15:47

I didn't have real tables to use, so I declare table variables. I don't have to go back and delete them later. You would just replace the table variable names and column names with the correct names.

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Stevan2020
Starting Member

25 Posts

Posted - 2010-08-19 : 18:09:28

Again thanks for the assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources