Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 782
  • Last Modified:

T-SQL - Pivot with multiple aggregates

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
Barry Cunney
Asked:
Barry Cunney
  • 2
  • 2
1 Solution
 
Barry CunneyAuthor Commented:
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
 
LowfatspreadCommented:
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
 
LowfatspreadCommented:
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
 
Barry CunneyAuthor Commented:
Thank you very much - gave me exactly what I needed and now my business analyst is very happy with his data :-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now