Solved

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

Posted on 2011-02-23
2
345 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
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
View SQL 2005 Job package 16 44
Increment column based of a FK 8 23
SQL Server Connection String through a VPN 8 29
Sql query with where clause 2 13
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 wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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