SQL Unpivoting data-CodePudding

I am trying to pivot column values (Month_End) which I have done per a few KB's but then I also need to unpivot other columns (PAC_Contract, PAC_Projected_Cost, JTD_Actual) into row values but am getting confused and not sure if PIVOT is the right want to attack this.

SQL2012

This is the Table

This is what I would like to get to

This is where I am currently at but it seems like the scenic route :)

DECLARE @columns NVARCHAR(MAX) = '',
        @sql     NVARCHAR(MAX) = '';

-- Get the last 3 Month End Dates
SELECT TOP 3
    @columns  = QUOTENAME(Month_End)   ','

FROM _rec_spectrum_CTC_Master
GROUP BY Month_End
ORDER BY Month_End desc;

-- Remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

PRINT @columns;

-- Construct the PIVOT
SET @sql ='
    SELECT * FROM(
        SELECT
        Job_Number,
        Month_End,
        ''PAC Contract'' AS Column_Name,
        PAC_Contract AS Value
    FROM _rec_spectrum_CTC_Master WHERE Job_Number = ''A1327''
    UNION
    SELECT
      Job_Number,
      Month_End,
    ''PAC Projected Cost'' AS Column_Name,
    PAC_Projected_Cost AS Value
    FROM _rec_spectrum_CTC_Master WHERE Job_Number = ''A1327''
    UNION
    SELECT
      Job_Number,
      Month_End,
      ''JTD_Actual_Cost'' AS Column_Name,
      JTD_Actual_Cost AS Value
    FROM _rec_spectrum_CTC_Master WHERE Job_Number = ''A1327''
    ) d
    PIVOT(
        SUM(Value)
        FOR Month_End IN ('  @columns  ')
    ) AS ppc ;';

EXECUTE sp_executesql @sql

CodePudding user response：

You would typically unpivot the columns to rows, then pivot the dates against new column:

select x.col
    max(case when month_end = '2022-06-01' then val end) as val_06_01,
    max(case when month_end = '2022-07-01' then val end) as val_07_01,
    max(case when month_end = '2022-08-01' then val end) as val_08_01
from mytable t
cross apply ( values 
    ('PAC_Contract', t.pac_contract), 
    ('PAC_PROJECTED_COST', t.pac_projected_cost), 
    ('JTD_Actual_Cost', t.jtd_actual_cost) 
) x(col, val)
group by x.col

Note that this only works on a fixed list of initial columns and dates ; if you want something more flexible than that, then you need dynamic SQL.

CodePudding user response：

I don't think there is any way to avoid dynamic SQL.

If the Pivot works, I see no particular reason not to use it. If you prefer a bit more control, you can use dynamic SQL to generate select items with conditional aggregation similar to what GMB has shown, but I believe you should use SUM() instead of MAX().

As GMB also suggested, CROSS APPLY (VALUES ...) is a simpler way to unpivot your original data vs multiple UNIONed selects. SQL does have an UNPIVOT clause, but it might not have the flexibility that CROSS APPLY (VALUES ...) gives you.

Lastly, you can also pass parameters (such as job number) into your dynamic SQL by declaring the parameter(s) and passing the value(s) in the EXECUTE sp_executesql statement.

-- Dynamic SQL with conditional aggregation
DECLARE @columns NVARCHAR(MAX) = '',
        @sql     NVARCHAR(MAX) = '';

SELECT TOP 3
    @columns  = '
        , SUM(CASE WHEN M.Month_End = '''
          CONVERT(VARCHAR(10), Month_End, 20)
          ''' THEN A.Value END) AS '
          QUOTENAME(Month_End)
FROM _rec_spectrum_CTC_Master
GROUP BY Month_End
ORDER BY Month_End desc;

SET @sql ='
    SELECT M.Job_Number, A.Column_Name'   @columns   '
    FROM _rec_spectrum_CTC_Master M
    CROSS APPLY (
        VALUES
            (''PAC Contract'', M.PAC_Contract),
            (''PAC Projected Cost'', M.PAC_Projected_Cost),
            (''JTD_Actual_Cost'', M.JTD_Actual_Cost)
    ) A(Column_Name, Value)
    WHERE M.Job_Number = @JobNo
    GROUP BY M.Job_Number, A.Column_Name
    ORDER BY M.Job_Number, A.Column_Name';

PRINT @sql
EXECUTE sp_executesql @sql, N'@JobNo VARCHAR(100)', @JobNo = 'A1327'

This will generate dynamic SQL like:

    SELECT M.Job_Number, A.Column_Name
        , SUM(CASE WHEN M.Month_End = '2022-08-01' THEN A.Value END) AS [2022-08-01]
        , SUM(CASE WHEN M.Month_End = '2022-07-01' THEN A.Value END) AS [2022-07-01]
        , SUM(CASE WHEN M.Month_End = '2022-06-01' THEN A.Value END) AS [2022-06-01]
    FROM _rec_spectrum_CTC_Master M
    CROSS APPLY (
        VALUES
            ('PAC Contract', M.PAC_Contract),
            ('PAC Projected Cost', M.PAC_Projected_Cost),
            ('JTD_Actual_Cost', M.JTD_Actual_Cost)
    ) A(Column_Name, Value)
    WHERE M.Job_Number = @JobNo
    GROUP BY M.Job_Number, A.Column_Name
    ORDER BY M.Job_Number, A.Column_Name

See This db<>fiddle for a working demo.