Solved

T-SQL - Pivot with multiple aggregates

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 35
TSQL XML Namespaces 7 24
First Max value 3 32
partitioning database after decade growth 8 29
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

821 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