What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!
Course Of The Month
1 day, 22 hours left to enrollBecome 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
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
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| SQL Stored Proc - Performance Enhancement | 15 | 86 | |
| T-SQL: Please describe what a page split is | 5 | 58 | |
| Need to replicate a Log table | 4 | 43 | |
| first parameter x of the decimal (x, y) | 1 | 30 |
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
710 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.