[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-02-23
2
Medium Priority
?
350 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

650 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