Solved

SQL Pivot Table

Posted on 2013-01-21
4
318 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

932 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

9 Experts available now in Live!

Get 1:1 Help Now