Solved

# SQL Pivot Table

Posted on 2013-01-21
322 Views
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
Question by:parpaa
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

Author Comment

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

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
;
``````
HTH
David
0

LVL 26

Assisted Solution

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

ID: 38805630
Thanks guys,

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

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
###### Suggested Courses
Course of the Month1 day, 22 hours left to enroll