SQL Pivot Table

Hi,

I am trying to  run the following resultset
Year      Quarter      Score      Name
2012      1      45      ABC
2012      2      22      ABC
2012      3      32      ABC
2012      4      45      ABC
2011      1      45      ABC
2011      2      22      ABC
2011      3      32      ABC
2011      4      45      ABC

into this below format.




      2012      2012      2012      2012      2011      2011      2011      2011
Name      1      2      3      4      1      2      3      4
ABC      45      22      32      45      45      22      32      45

I believe this can be done using Pivot, not sure how to get this with two headers (Year +Quarter).
Any thoughts is appreciated
parpaaAsked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

if object_id( N'tempdb..#t', N'U' ) is not null 
	drop table #t;
	
create table #t(
	CYear int
	, CQuarter int
	, CScore int
	, CName varchar( 30 )
	)
	
insert #t( CYear, CQuarter, CScore, CName )
	values( 2012, 1, 45, 'ABC' )
	, ( 2012, 2, 22, 'ABC' )
	, ( 2012, 3, 32, 'ABC' )
	, ( 2012, 4, 45, 'ABC' )
	, ( 2011, 1, 45, 'ABC' )
	, ( 2011, 2, 22, 'ABC' )
	, ( 2011, 3, 32, 'ABC' )
	, ( 2011, 4, 45, 'ABC' )
;

select *
from #t
;

select
	CName
	, [2012 Q4]
	, [2012 Q3]
	, [2012 Q2]
	, [2012 Q1]
	, [2011 Q4]
	, [2011 Q3]
	, [2011 Q2]
	, [2011 Q1]
from
	(
	select
		convert( char( 4 ), t.CYear ) + ' Q' + convert( char( 1 ), t.CQuarter ) YYQQ
		, t.CScore
		, t.CName
	from #t t
	) tt
pivot (
	sum( CScore )
	for YYQQ in (
		[2012 Q4]
		, [2012 Q3]
		, [2012 Q2]
		, [2012 Q1]
		, [2011 Q4]
		, [2011 Q3]
		, [2011 Q2]
		, [2011 Q1]
	)
) as pvt
;

Open in new window

HTH
  David
0
 
parpaaAuthor Commented:
Or the below format will also do
Name      2012 Q1      2012 Q2      2012 Q3      2012 Q4      2011 Q1      2011 Q2      2011 Q3      2011 Q4
ABC      45      22      32      45      45      22      32      45
0
 
Chris LuttrellSenior Database ArchitectCommented:
dtodd has your answer for a basic static PIVOT statement.  
In case your years and quarters will be changing, like you always want the past 2 years or last 8 quarters, then there is an excelent article on doing a Dynamic Pivot table by mark_wills at Article:653:Dynamic-Pivot-Procedure-for-SQL-Server.
He explains how to create your column list on the fly for a running total group and use it in a PIVOT statement built with Dynamic SQL.
0
 
parpaaAuthor Commented:
Thanks guys,

Dtodd - Your solution served my purpose
Indeed it was great post on Dynamic Pivot SQL, thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here