Solved

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

Posted on 2011-02-23
2
343 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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now