Solved

T-SQL - Pivot with multiple aggregates

Posted on 2011-02-11
4
711 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

910 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

17 Experts available now in Live!

Get 1:1 Help Now