SQLServer - Pivoting a table with Group

Question

I am wondering if what I am trying to do is possible. I believe it is using the PIVOT function in TSQL but don't have enough experience with the PIVOT function to know where to start.

Basically I'm trying to take the following # table called #tmpbudgetdata (truncated for simplicity):

Account              Description                                                                                          BudgetAmount          Period
-------------------- ---------------------------------------------------------------------------------------------------- --------------------- --------------------
4001                 Mood Embedded Account                                                                                0.00                  1
4001                 Mood Embedded Account                                                                                0.00                  2
4001                 Mood Embedded Account                                                                                0.00                  3
4001                 Mood Embedded Account                                                                                0.00                  4
4001                 Mood Embedded Account                                                                                0.00                  5
4001                 Mood Embedded Account                                                                                0.00                  6
4001                 Mood Embedded Account                                                                                0.00                  7
4001                 Mood Embedded Account                                                                                0.00                  8
4001                 Mood Embedded Account                                                                                0.00                  9
4001                 Mood Embedded Account                                                                                0.00                  10
4001                 Mood Embedded Account                                                                                0.00                  11
4001                 Mood Embedded Account                                                                                0.00                  12
4003                 DBS Music                                                                                            0.00                  1
4003                 DBS Music                                                                                            0.00                  2
4003                 DBS Music                                                                                            0.00                  3
4003                 DBS Music                                                                                            0.00                  4
4003                 DBS Music                                                                                            0.00                  5
4003                 DBS Music                                                                                            0.00                  6
4003                 DBS Music                                                                                            0.00                  7
4003                 DBS Music                                                                                            0.00                  8
4003                 DBS Music                                                                                            0.00                  9
4003                 DBS Music                                                                                            0.00                  10
4003                 DBS Music                                                                                            0.00                  11
4003                 DBS Music                                                                                            0.00                  12
4010                 Sales - Software                                                                                     5040.00               1
4010                 Sales - Software                                                                                     0.00                  2
4010                 Sales - Software                                                                                     6280.56               3
4010                 Sales - Software                                                                                     6947.93               4
4010                 Sales - Software                                                                                     4800.00               5
4010                 Sales - Software                                                                                     0.00                  6
4010                 Sales - Software                                                                                     2400.00               7
4010                 Sales - Software                                                                                     2550.00               8
4010                 Sales - Software                                                                                     4800.00               9
4010                 Sales - Software                                                                                     2400.00               10
4010                 Sales - Software                                                                                     0.00                  11
4010                 Sales - Software                                                                                     2400.00               12
4015                 New Install Revenue                                                                                  0.00                  1
4015                 New Install Revenue                                                                                  0.00                  2
4015                 New Install Revenue                                                                                  0.00                  3
4015                 New Install Revenue                                                                                  3844.79               4
4015                 New Install Revenue                                                                                  0.00                  5
4015                 New Install Revenue                                                                                  0.00                  6
4015                 New Install Revenue                                                                                  0.00                  7
4015                 New Install Revenue                                                                                  0.00                  8
4015                 New Install Revenue                                                                                  0.00                  9
4015                 New Install Revenue                                                                                  0.00                  10
4015                 New Install Revenue                                                                                  0.00                  11
4015                 New Install Revenue                                                                                  0.00                  12

and turning it into something like this:

Account Description      Period1 Period2 Period3 Period4 Period5 Period6 Period7 Period8 Period9 Period10 Period11 Period12
------- --------------- -------- ------- -------- ------ ------- ------- -------- ------ ------- -------- -------- --------
4001   Mood Enabled...  0.00     0.00    0.00    0.00    0.00     0.00   0.00    0.00     0.00    0.00    0.00    0.00
4003   Dbs Music        0.00     0.00    0.00    0.00    0.00     0.00   0.00    0.00     0.00    0.00    0.00    0.00 
4010   Sales - Software 5040.00  0.00    6280.56 6947.93 4800.00  0.00   2400.00 2550.00  4800.00 2400.00 0.00    2400.00
...etc...

Basically just grouping via the Account column (the description is the same per account) and then taking the period values and pivoting them horizontally.

I know I could do it with a cursor and loop through but wondering if this is possible with a pivot or by other means.

Thanks in advance

Answer 1

I simple PIVOT should do the trick

Example

Select *
 From  (
        Select [Account] 
              ,[Description]
              ,Period = concat('Period',Period)
              ,[BudgetAmount]
          From YourTable
       ) src 
 Pivot (sum([BudgetAmount]) for Period in ( [Period1],[Period2],[Period3],[Period4],[Period5],[Period6],[Period7],[Period8],[Period9],[Period10],[Period11],[Period12] ) ) pvt

Returns