Pivot struggle

[TheBugSlayer]

Good day all.
I need help implementing a pivot transformation. I have a table that looks like this:

[b]ProductLine			AccessionNumber				AccountingPeriod[/b]
Hematopathology / Oncology	EF2067FD-84D6-4653-A845-2BC411BB1810	201101
Hematopathology / Oncology	61077737-F8D9-4675-8E03-731CA1D3D515	201101
GI				52DA4BBF-CF16-4779-9E86-CA903B03E7AB	201101
GU				3DAD889C-5274-442C-B83C-D335C2F21490	201101
GI				52DA4BBF-CF16-4779-9E86-CA903B03E7AC	201101
GU				3DAD889C-5274-442C-B83C-D335C2F21495	201102

Data grouped by product line and accounting period using this query

SELECT top 10 ProductLine, COUNT(AccessionNumber) Total, L.AccountingPeriod
FROM  LabData L
JOIN ProductLineCoding PLC ON L.AccessPrefix = PLC.Prefix
WHERE AccountingPeriod IS NOT NULL
GROUP By ProductLine,  AccountingPeriod

would look like this:

[b]ProductLine	                Total	AccountingPeriod[/b]
Hematopathology / Oncology	2	201101
GI	                        2	201101
GU	                        1	201101
GU	                        1	201102

Now, I need to obtain something like this:

[b]Product Line	                201101	201102[/b]
Hematopathology / Oncology	2	0
GI	                        2	0
GU	                        1	1

When I run my query (or permutations thereof)

SELECT ProductLine, '201101','201102','201103','201104','201105','201106','201107','201108','201109','201110','201111','201112',
'201201','201202','201203','201204','201205','201206','201207','201208','201209','201210','201211','201212'
FROM  
(
	SELECT ProductLine, L.AccessionNumber, L.AccountingPeriod
	FROM  LabData L
	JOIN ProductLineCoding PLC ON L.AccessPrefix = PLC.Prefix
	GROUP By ProductLine,  L.AccountingPeriod
) P
PIVOT
(
	COUNT(AccessionNumber)
	FOR AccountingPeriod IN 
	([201101],[201102],[201103],[201104],[201105],[201106],[201107],[201108],[201109],[201110],[201111],[201112],
	 [201201],[201202],[201203],[201204],[201205],[201206],[201207],[201208],[201209],[201210],[201211],[201212])
) AS Pvt

it complains that Column 'LabData.AccessionNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. or it returns a row with the accounting period, etc...Never what I want. Can someone help?

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[imex]

Hi,

Try to completely remove the Group By clause.

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]