CODEWITHSUNDEEP
sql-server
Rod
Rod

Reputation: 15475

How do I produce the expected table?

I'm trying to get averages of min and max for each categories in posnam column. This record set is of course only a sample, so there are many more records.

Given:

state   position    minrate maxrate
ny  admin assistant 12.5000 14.5000
ny  office manager  20.5000 25.5000
ca  admin assistant 13.5000 15.5000
ca  office manager  21.5000 26.5000
al  admin assistant 11.5000 13.5000
al  office manager  19.5000 24.5000

Expected:

position    ny_min  ny_max  ca_min  ca_max  al_min  al_max  avg_min avg_max
admin assistant 12.5000 14.5000 13.5000 15.5000 11.5000 13.5000 12.5000 14.5000
office manager  20.5000 25.5000 21.5000 26.5000 19.5000 24.5000 20.5000 25.5000

Code:

declare @jobs table (
[state] nvarchar(25),
[position] nvarchar(25),
[minrate] decimal(18,4),
[maxrate] decimal(18,4)
)

insert @jobs
values
('ny','admin assistant',12.5, 14.5),
('ny','office manager',20.5, 25.5),
('ca','admin assistant',13.5, 15.5),
('ca','office manager',21.5, 26.5),
('al','admin assistant',11.5, 13.5),
('al','office manager',19.5, 24.5)

select * from @jobs

Upvotes: 0

Views: 146

Answers (1)

trenton-ftw
trenton-ftw

Reputation: 972

In order to dynamically create field names, you will need to utilize dynamic SQL. To pair that with the additional aggregates that you need (the total avg min/max), you will need to perform an additional query across all rows and combine them.

In order to utilize dynamic SQL in this manner, we need an object outside of the current session's scope, so for the purpose of your example here I have swapped your provided table variable of @jobs for a global temp table ##tmpjobs. Assuming that you are actually pulling this from a database table in the real world, you can simply swap the global temp table ##tmpjobs for your real table.

I accomplish this in the below example by unpivoting all state min/max values, adding unpivoted values for the total avg min/max, and then performing a single (fairly standard) PIVOT command.

/*get list of columns that we want for our pivot*/
DECLARE @ColumnList nvarchar(max) = CONCAT((
SELECT 
    STRING_AGG(state_list.min_max_title,N',') WITHIN GROUP (ORDER BY state_list.min_max_title DESC) AS ColumnList 
FROM 
    (SELECT 
        CONCAT(j.[state],N'_min,',j.[state],N'_max') AS min_max_title
    FROM 
        ##tmpjobs AS j
    GROUP BY 
        j.[state]) AS state_list 
),N',avg_min,avg_max'); 

/*build pivot query*/
DECLARE @Sql nvarchar(max) = CONCAT(
N'SELECT 
    pvt.* 
FROM 
    /*subquery to unpivot data for min/max values for each state and the two totals*/
    (/*add in min for each state*/
    SELECT 
        CONCAT(j.[state],N''_min'') AS ColumnName 
        ,j.position AS position  
        ,j.minrate AS Amount 
    FROM 
        ##tmpjobs AS j
    UNION ALL 
    /*add max for each state*/
    SELECT 
        CONCAT(j.[state],N''_max'') AS ColumnName 
        ,j.position AS position  
        ,j.maxrate AS Amount 
    FROM 
        ##tmpjobs AS j
    UNION ALL 
    /*add total min/max rows*/
    SELECT 
        CASE /*conditionally return max/min column name*/
            WHEN row_mult.RowId = 1 THEN ''avg_min'' 
            WHEN row_mult.RowId = 2 THEN ''avg_max'' 
        END AS ColumnName 
        ,total_avgs.position
        ,CASE /*conditionally return max/min value*/
            WHEN row_mult.RowId = 1 THEN total_avgs.AvgMinRate
            WHEN row_mult.RowId = 2 THEN total_avgs.AvgMaxRate
        END
    FROM 
        /*subquery to calculate the total for all states for each position*/
        (SELECT 
            j.position 
            ,AVG(j.minrate) AS AvgMinRate 
            ,AVG(j.maxrate) AS AvgMaxRate 
        FROM 
            ##tmpjobs AS j
        GROUP BY 
            j.position) AS total_avgs 
        /*generate an extra row per position*/
        OUTER APPLY (SELECT 1 AS RowId 
                     UNION ALL 
                     SELECT 2 AS RowId) AS row_mult) AS src 
    PIVOT 
    (MAX(Amount) FOR ColumnName IN (',@ColumnList,N')) AS pvt');

/*now run query*/
EXEC sys.sp_executesql @stmt = @Sql;

The only thing to really note here is that I have the state columns currently in reverse alphabetic order (Z to A) to match your expected output. You can change that to A to Z by changing the DESC order to ASC in the WITHIN GROUP statement, or really any other order you please by changing what the @ColumnList variable outputs.

Upvotes: 1

Related Questions