Solved

T-SQL - Pivot with multiple aggregates

Posted on 2011-02-11
4
697 Views
Last Modified: 2012-05-11
Hi I have data in the followign format
Asse     Read Month   Mono Volume   Color Volume
F4TTY      1      90799      58918
F4TTY      2      50802      91520
F4TTY      3      51889      76703
F4TTY      4      31045      79403
F4TTY      5      66776      65872
F4TTY      6      69763      44267
F4TTY      7      44770      47430
F4TTY      8      8407      35886
F4TTY      9      41977      32080
F4TTY      10      8259      59419
F4TTY      11      38253      32424
F4TTY      12      72319      31331
AKLLU      1      74629      58657
AKLLU      2      33532      26630
AKLLU      3      71802      26755
AKLLU      4      13580      43241
AKLLU      5      71935      71526
AKLLU      6      37367      21174
AKLLU      7      84601      56785
AKLLU      8      64520      83330
AKLLU      9      86890      72911
AKLLU      10      65824      62794
AKLLU      11      49271      93008
AKLLU      12      13187      79665

and I wish to pivot it into this format
Asset Number      1            2            3      
AKLLU                        74629                         58657                        33532      
F4TTY                        90799                         58918                        50802      

using T-SQL
0
Comment
Question by:Barry Cunney
  • 2
  • 2
4 Comments
 
LVL 17

Author Comment

by:Barry Cunney
ID: 34875434
I want the Read Month to become the columns, but very every read month I want both the Mono Volume Total and the Color Volume total
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34875546
you have to do it as two pivots and then join them

like this
select coalesce(a.Asset,b.asset) as Asset
       ,A.[1] as [Mono 1],B.[1] as [Color 1]
,A.[2] as [Mono 2],B.[2] as [Color 2]
,A.[3] as [Mono 3],B.[3] as [Color 3]
,A.[4] as [Mono 4],B.[4] as [Color 4]
,A.[5] as [Mono 5],B.[5] as [Color 5]
,A.[6] as [Mono 6],B.[6] as [Color 6]
,A.[7] as [Mono 7],B.[7] as [Color 7]
,A.[8] as [Mono 8],B.[8] as [Color 8]
,A.[9] as [Mono 9],B.[9] as [Color 9]
,A.[10] as [Mono 10],B.[10] as [Color 10]
,A.[11] as [Mono 11],B.[11] as [Color 11]
,A.[12] as [Mono 12],B.[12] as [Color 12] 
  from (select Asset,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
         from yourtable as x
        pivot (sum([Mono Volume]) 
                 for [Read Month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as P
       ) as A
  Full Outer Join
       (select Asset,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
         from yourtable as x
        pivot (sum([Color Volume]) 
                 for [Read Month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as P
       ) as B
    on A.Asset=B.Asset
 Order by 1

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34875913
or perhaps this ...
select coalesce(a.Asset,b.asset) as Asset
,coalesce(A.[1],0) as [Mono 1],coalesce(B.[1] ,0) as [Color 1]
,coalesce(A.[2],0) as [Mono 2],coalesce(B.[2] ,0) as [Color 2]
,coalesce(A.[3],0) as [Mono 3],coalesce(B.[3] ,0) as [Color 3]
,coalesce(A.[4],0) as [Mono 4],coalesce(B.[4] ,0) as [Color 4]
,coalesce(A.[5],0) as [Mono 5],coalesce(B.[5] ,0) as [Color 5]
,coalesce(A.[6],0) as [Mono 6],coalesce(B.[6] ,0) as [Color 6]
,coalesce(A.[7],0) as [Mono 7],coalesce(B.[7] ,0) as [Color 7]
,coalesce(A.[8],0) as [Mono 8],coalesce(B.[8] ,0) as [Color 8]
,coalesce(A.[9],0) as [Mono 9],coalesce(B.[9] ,0) as [Color 9]
,coalesce(A.[10],0) as [Mono 10],coalesce(B.[10] ,0) as [Color 10]
,coalesce(A.[11],0) as [Mono 11],coalesce(B.[11] ,0) as [Color 11]
,coalesce(A.[12],0) as [Mono 12],coalesce(B.[12] ,0) as [Color 12]
  from (select Asset,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
         from yourtable as x
        pivot (sum([Mono Volume]) 
                 for [Read Month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as P
       ) as A
  Full Outer Join
       (select Asset,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
         from yourtable as x
        pivot (sum([Color Volume]) 
                 for [Read Month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as P
       ) as B
    on A.Asset=B.Asset
 Order by 1

Open in new window

0
 
LVL 17

Author Closing Comment

by:Barry Cunney
ID: 34920304
Thank you very much - gave me exactly what I needed and now my business analyst is very happy with his data :-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query Assistance 4 31
SQL Query Syntax Error 9 33
Query Missing Money orders... 6 72
Update in Sql 7 0
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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

758 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

22 Experts available now in Live!

Get 1:1 Help Now