Solved

SQL Pivot Table

Posted on 2013-01-21
4
319 Views
Last Modified: 2013-01-22
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
0
Comment
Question by:parpaa
  • 2
4 Comments
 

Author Comment

by:parpaa
ID: 38801781
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
 
LVL 35

Accepted Solution

by:
David Todd earned 350 total points
ID: 38802334
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
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 150 total points
ID: 38803639
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
 

Author Closing Comment

by:parpaa
ID: 38805630
Thanks guys,

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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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