MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Pivot Table
Posted on 2013-01-21
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
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
[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
- Learn & ask questions
-
2
4 Comments
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
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
Hi,
David
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
;
David
Active today
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-
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.
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-
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 10 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
634 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.