Solved

T-SQL - Pivot with multiple aggregates

Posted on 2011-02-11
4
745 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
[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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

740 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