Solved

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

Posted on 2011-02-23
2
348 Views
Last Modified: 2012-05-11
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
Comment
Question by:ticgums
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 34966916
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
 

Author Comment

by:ticgums
ID: 34970421
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question