• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

PIVOT returning one line for each source record (not consolidating).

The attached code generates a properly formatted answer, but returns one row for each row in the inside SELECT.  The rows returned contain the correct value for one year and all other columns are NULL.   I have 13 years plus "9999" which signifies open orders (13 + 1 = 14 column headings).  There are 6 record types.  6 x 14 = 84 rows.  I expect this from the internal select, but expected this is be collapsed to 6 rows in the PIVOT process.

The attached spreadsheet contains the results of the UNION (pre-PIVOT) statement and the results of the PIVOT.

 
alter view dbo.vwX3_RecordCounts as
select RecTyp,[1999],[2000],[2001],[2002],[2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010],[2011],[9999]
	from (select RecTyp,yyyy,cast(yyyy as char(4)) as [Year],Cnt
	from (
		select 'So'		as RecTyp,year(t_odat)	as yyyy,count(*) as Cnt from ttdsls040100 H	group by year(t_odat)
		UNION --ALL
		select 'SoLine' as RecTyp,year(t_odat)	as yyyy,count(*) as Cnt from ttdsls041100 L	group by year(t_odat)
		UNION --ALL
		select 'Po'		as RecTyp,year(t_odat)	as yyyy,count(*) as Cnt from ttdpur040100 H	where t_orno not between 400000 and 499999
																							group by year(t_odat)
		UNION --ALL
		select 'PoLine' as RecTyp,year(t_odat)	as yyyy,count(*) as Cnt from ttdpur041100 L	where t_orno not between 400000 and 499999
																							group by year(t_odat)
		UNION --ALL
		select 'Wo'		as RecTyp,year(t_efdt)	as yyyy,count(*) as Cnt from tpspmg001100 W	group by year(t_efdt)
		UNION --ALL
		select 'WoCmpt' as RecTyp,year(t_efdt)	as yyyy,count(*) as Cnt from tpspmg010100 C 
																  inner join tpspmg001100 W on C.t_orno = W.t_orno
																							group by year(t_efdt)
		UNION --ALL
		select 'So'		as RecTyp,9999			as yyyy,count(*) as Cnt from ttdsls040100 H	where H.t_orno in (select t_orno from ttdsls041100	where t_odat > '2009-01-01' and t_dqua = 0)
		UNION --ALL
		select 'SoLine' as RecTyp,9999			as yyyy,count(*) as Cnt from ttdsls041100 L														where t_odat > '2009-01-01' and t_dqua = 0
		UNION --ALL
		select 'Po'		as RecTyp,9999			as yyyy,count(*) as Cnt from ttdpur040100 H	where H.t_orno in (select t_orno from ttdpur041100	where t_orno not between 400000 and 499999
																																				  and t_odat > '2009-01-01' and t_dqua = 0)
		UNION --ALL
		select 'PoLine' as RecTyp,9999			as yyyy,count(*) as Cnt from ttdpur041100 L														where t_orno not between 400000 and 499999
																																				  and t_odat > '2009-01-01' and t_dqua = 0
		UNION --ALL
		select 'Wo'		as RecTyp,9999			as yyyy,count(*) as Cnt from tpspmg001100 W	where t_osta < 7
		UNION --ALL
		select 'WoCmpt' as RecTyp,9999			as yyyy,count(*) as Cnt from tpspmg010100 C 
																  inner join tpspmg001100 W on C.t_orno = W.t_orno
																							where t_osta < 7
	) Y ) Z
PIVOT ( sum(Cnt)
	FOR [Year]
	IN ([1999],[2000],[2001],[2002],[2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010],[2011],[9999])) as PVT
/*
select * from vwX3_RecordCounts
*/

Open in new window


 PIVOT-problem.xlsx
0
ticgums
Asked:
ticgums
1 Solution
 
Mark WillsTopic AdvisorCommented:
It is the inclusion of YYYY I think in the initial select.

Because YYYY is not part of the aggregation then it is being used as part of the "group by" along with rectype.

So instead of :
               from (select RecTyp,yyyy,cast(yyyy as char(4)) as [Year],Cnt

Open in new window


try :
               from (select RecTyp,cast(yyyy as char(4)) as [Year],Cnt

Open in new window



And really, you dont have to do the cast as [year] you could simply use the yyyy column directly (because the actual years are spelled out as column names)
0
 
ticgumsAuthor Commented:
That did it.  Thank you VERY much.  My reason for casting as char was an earlier problem.  I did not realize that a column that was not referenced could cause this problem.

I removed the cast and it works with the integer yyyy column.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now