TSQL PIVOT with multiple pivoted and unpivoted columns?

Question

I have this SAMPLE data:

PERIOD FINANCE TYPE  SEGMENT  DEPARTMENT         PERIOD VALUE   ACCUMULATED VALUE
1       Actual       HE       Business           -9394.6         -9394.6
2       Actual       HE       Business           141646.6        132252
3       Actual       HE       Business           145920.25       278172.25
1       Actual       HE       Business Skills     0              0
2       Actual       HE       Business Skills     0              0
3       Actual       HE       Business Skills     0              0
1       Actual    SERVICES    Catering Services  32359.59        32359.59
2       Actual    SERVICES    Catering Services  147628.38       179987.97
3       Actual    SERVICES    Catering Services  174413.87       354401.84
1       Budget       HE       Business           139812.27       139812.27
2       Budget       HE       Business           139812.27       279624.54
3       Budget       HE       Business           139812.27       419436.81
1       Budget       HE       Business Skills     0              0
2       Budget       HE       Business Skills     0              0
3       Budget       HE       Business Skills     0              0
1       Budget    SERVICES    Catering Services  137500          137500
2       Budget    SERVICES    Catering Services  137500          275000
3       Budget    SERVICES    Catering Services  137500          412500

I need to have it in this format:

FINANCE TYPE  SEGMENT   DEPARTMENT          PERIOD VALUE 1  PERIOD VALUE 2  PERIOD VALUE 3  ACCUMULATED VALUE 1 ACCUMULATED VALUE 2 ACCUMULATED VALUE 3
Actual         HE       Business            -9394.6         141646.6        145920.25             -9394.6              132252           278172.25
Actual         HE       Business Skills     0               0               0   0   0   0
Actual       SERVICES   Catering Services   32359.59        147628.38       174413.87   32359.59    179987.97   354401.84
Budget         HE       Business            139812.27       139812.27       139812.27   139812.27   279624.54   419436.81
Budget         HE       Business Skills     0               0               0   0   0   0
Budget       SERVICES   Catering Services   137500          137500          137500  137500  275000  412500

I've tried the following code, but get several NULL values where there shouldn't be and the output is just wrong. I'm still trying to understand the use of cross apply and pivots in general.

SELECT [FINANCE TYPE], SEGMENT, 
  DEPARTMENT, [PERIOD VALUE1], 
  [PERIOD VALUE2], [PERIOD VALUE3], 
  [PERIOD VALUE4], [PERIOD VALUE5], 
  [PERIOD VALUE6], [PERIOD VALUE7], 
  [PERIOD VALUE8], [PERIOD VALUE9], 
  [PERIOD VALUE10], [PERIOD VALUE11], 
  [PERIOD VALUE12], [ACCUMULATED VALUE1], 
  [ACCUMULATED VALUE2], [ACCUMULATED VALUE3], 
  [ACCUMULATED VALUE4], [ACCUMULATED VALUE5], 
  [ACCUMULATED VALUE6], [ACCUMULATED VALUE7], 
  [ACCUMULATED VALUE8], [ACCUMULATED VALUE9], 
  [ACCUMULATED VALUE10], [ACCUMULATED VALUE11], 
  [ACCUMULATED VALUE12] 
FROM 
( 
    SELECT PERIOD, [FINANCE TYPE], 
      SEGMENT, DEPARTMENT, 
      COL, VALUE, COL+PERIOD AS PRD 
    FROM CTE 
    CROSS APPLY 
    ( 
        SELECT 'PERIOD VALUE', [PERIOD VALUE] UNION ALL 
        SELECT 'ACCUMULATED VALUE', [ACCUMULATED VALUE] 
    )C(COL, VALUE) 
)D 
PIVOT 
( 
  MAX(VALUE) 
  FOR [PRD] IN ([PERIOD VALUE1], [PERIOD VALUE2], [PERIOD VALUE3], 
                [PERIOD VALUE4], [PERIOD VALUE5], [PERIOD VALUE6] , 
                [PERIOD VALUE7], [PERIOD VALUE8], [PERIOD VALUE9], 
                [PERIOD VALUE10], [PERIOD VALUE11], [PERIOD VALUE12] , 
                [ACCUMULATED VALUE1], [ACCUMULATED VALUE2], 
                [ACCUMULATED VALUE3], [ACCUMULATED VALUE4], 
                [ACCUMULATED VALUE5], [ACCUMULATED VALUE6] , 
                [ACCUMULATED VALUE7], [ACCUMULATED VALUE8], 
                [ACCUMULATED VALUE9], [ACCUMULATED VALUE10], 
                [ACCUMULATED VALUE11], [ACCUMULATED VALUE12]) 
)PIV

Any help to help me solve this would be greatly appreciated.

Answer 1

I see a few things wrong with your existing code. First, in your subquery you have col and Period repeated - once it is being using to create the new column names with the Period and once on its own. That will alter your results because the col and Period on its own will be grouped by, so if you don't use it in the final result - don't include it in the subquery.

The code should be altered to the following:

SELECT [FINANCE TYPE], SEGMENT, 
  DEPARTMENT, [PERIOD VALUE1], 
  [PERIOD VALUE2], [PERIOD VALUE3], 
  [PERIOD VALUE4], [PERIOD VALUE5], 
  [PERIOD VALUE6], [PERIOD VALUE7], 
  [PERIOD VALUE8], [PERIOD VALUE9], 
  [PERIOD VALUE10], [PERIOD VALUE11], 
  [PERIOD VALUE12], [ACCUMULATED VALUE1], 
  [ACCUMULATED VALUE2], [ACCUMULATED VALUE3], 
  [ACCUMULATED VALUE4], [ACCUMULATED VALUE5], 
  [ACCUMULATED VALUE6], [ACCUMULATED VALUE7], 
  [ACCUMULATED VALUE8], [ACCUMULATED VALUE9], 
  [ACCUMULATED VALUE10], [ACCUMULATED VALUE11], 
  [ACCUMULATED VALUE12] 
FROM 
( 
    SELECT [FINANCE TYPE], 
      SEGMENT, DEPARTMENT, 
      VALUE, 
      COL+cast(PERIOD as varchar(10)) AS PRD 
    FROM yourtable 
    CROSS APPLY 
    ( 
        SELECT 'PERIOD VALUE', [PERIOD VALUE] UNION ALL 
        SELECT 'ACCUMULATED VALUE', [ACCUMULATED VALUE] 
    )C(COL, VALUE) 
)D 
PIVOT 
( 
  MAX(VALUE) 
  FOR [PRD] IN ([PERIOD VALUE1], [PERIOD VALUE2], [PERIOD VALUE3], 
                [PERIOD VALUE4], [PERIOD VALUE5], [PERIOD VALUE6] , 
                [PERIOD VALUE7], [PERIOD VALUE8], [PERIOD VALUE9], 
                [PERIOD VALUE10], [PERIOD VALUE11], [PERIOD VALUE12] , 
                [ACCUMULATED VALUE1], [ACCUMULATED VALUE2], 
                [ACCUMULATED VALUE3], [ACCUMULATED VALUE4], 
                [ACCUMULATED VALUE5], [ACCUMULATED VALUE6] , 
                [ACCUMULATED VALUE7], [ACCUMULATED VALUE8], 
                [ACCUMULATED VALUE9], [ACCUMULATED VALUE10], 
                [ACCUMULATED VALUE11], [ACCUMULATED VALUE12]) 
)PIV

See SQL Fiddle with Demo