Pivoting the employee column

Question

I have a table like so.

I need the final result show only one record for each order (essentially combine the suborders). Only sum up the revenue for distinct suborders (for order 0935744, sum up 575.04 + 31.68). An order can have a maximum of 2 employees

Final result should be like this:

order      totalrevenue  employee1 employee2 
0813700     258.57        CREW      NULL      
0935744     606.72        95liv     95nat  

I've tried using row_number and doing some joins but I've had no luck.

Sample code

SELECT N'0813700' AS [OrderNum], N'1077980' AS [SubOrder], N'CREW' AS [employeeid], N'258.57' AS [revenue] UNION ALL
SELECT N'0935744' AS [OrderNum], N'1257060' AS [SubOrder], N'95LIV' AS [employeeid], N'575.04' AS [revenue] UNION ALL
SELECT N'0935744' AS [OrderNum], N'1342944' AS [SubOrder], N'95LIV' AS [employeeid], N'31.68' AS [revenue] UNION ALL
SELECT N'0935744' AS [OrderNum], N'1257060' AS [SubOrder], N'95NAT' AS [employeeid], N'575.04' AS [revenue] UNION ALL
SELECT N'0935744' AS [OrderNum], N'1342944' AS [SubOrder], N'95NAT' AS [employeeid], N'31.68' AS [revenue]

Answer 1

How about this? (Revised after comment from OP)

Assumptions:

1. No more than two employees per order.
2. One employee per sub-order (as in the example)
3. Revenue for a sub order is consistently duplicated on rows for the suborder (per example)

Code Example

select order, sum(revenue) as totalrevenue, max(employee1) as employee1,
case
    when max(employee1) = max(employee2) then null
    else max(employee2)
end as employee2
from (
    select order, suborder, max(revenue) as revenue, max(employeeid)
    from orders
    group by order, suborder
) SubOrderTotal
group by order

Generally I would not recommend the rigid transform to two employees or the duplication of suborder revenue. Making such rigid assumptions often leads to bugs when dealing with real world data. But, I don't know your data.

Answer 2

this should give the result you are looking for:

create table #temp
(
    ordernum int,
    suborder int,
    employeeid varchar(50),
    revenue money
)

insert into #temp values(0813700, 1077980, 'CREW', 258.57)
insert into #temp values(0935744, 1257060, '95LIV', 575.04)
insert into #temp values(0935744, 1342944, '95LIV', 31.68)
insert into #temp values(0935744, 1257060, '95NAT', 575.04)
insert into #temp values(0935744, 1342944, '95NAT', 31.68)

select ordernum
    , sum(revenueperorder) as total
    , employee1
    , case when employee1 = employee2 then null else employee2 end as employee2
from 
(
    select ordernum
        , revenue as revenueperorder
        , min(employeeid) as employee1
        , max(employeeid) as employee2
    from #temp
    group by ordernum, revenue
) x
group by ordernum, employee1, employee2

drop table #temp

Results:

813700  258.57  CREW    NULL
935744  606.72  95LIV   95NAT

Answer 3

DECLARE @SubOrder TABLE
(
    OrderNum    INT NOT NULL,
    SubOrder    INT NOT NULL,
    EmployeeID  NVARCHAR(50) NOT NULL,
    Revenue     NUMERIC(10, 2) NOT NULL
);

INSERT  @SubOrder (OrderNum, SubOrder, EmployeeID, Revenue)
SELECT N'0813700' AS [OrderNum], N'1077980' AS [SubOrder], N'CREW' AS [employeeid], N'258.57' AS [revenue] UNION ALL
SELECT N'0935744' AS [OrderNum], N'1257060' AS [SubOrder], N'95LIV' AS [employeeid], N'575.04' AS [revenue] UNION ALL
SELECT N'0935744' AS [OrderNum], N'1342944' AS [SubOrder], N'95LIV' AS [employeeid], N'31.68' AS [revenue] UNION ALL
SELECT N'0935744' AS [OrderNum], N'1257060' AS [SubOrder], N'95NAT' AS [employeeid], N'575.04' AS [revenue] UNION ALL
SELECT N'0935744' AS [OrderNum], N'1342944' AS [SubOrder], N'95NAT' AS [employeeid], N'31.68' AS [revenue];

SELECT  pvt.OrderNum,
        pvt.TotalRevenue,
        pvt.[1] AS Emp1,
        pvt.[2] AS Emp2
FROM
(
        SELECT  dt.OrderNum,
                dt.EmployeeID,
                DENSE_RANK()    OVER(PARTITION BY dt.OrderNum ORDER BY dt.EmployeeID) AS Rnk,
                SUM(dt.Revenue) OVER(PARTITION BY dt.OrderNum) AS TotalRevenue
        FROM
        (
                SELECT  so.OrderNum,
                        so.EmployeeID,
                        ROW_NUMBER() OVER(PARTITION BY so.OrderNum, so.SubOrder ORDER BY @@SPID) AS RowNum,
                        so.Revenue
                FROM    @SubOrder so
        ) dt
        WHERE   dt.RowNum = 1
) src
PIVOT   ( MAX(src.EmployeeID) FOR src.Rnk IN ([1], [2]) ) pvt

Results:

OrderNum TotalRevenue Emp1   Emp2
-------- ------------ ------ -----
813700   258.57       CREW   NULL
935744   606.72       95LIV  95NAT

Intermediate results (...) src:

OrderNum EmployeeID Rnk TotalRevenue
-------- ---------- --- ------------
813700   CREW       1   258.57
935744   95LIV      1   606.72
935744   95NAT      2   606.72

Intermediate results (...) dt:

OrderNum EmployeeID RowNum Revenue
-------- ---------- ------ -------
813700   CREW       1      258.57
935744   95LIV      1      575.04
935744   95NAT      2      575.04
935744   95NAT      1      31.68
935744   95LIV      2      31.68

Answer 4

Answers so far would require a hard coded pivot with employee1 & 2 defined as pivot entities, of couse, if your data is dynamic i'd imagine you'll have a varying number of employees (and thus would need a varying number of columns)? If so, i'd suggest you adopt a hybrid dynamic SQL / Pivot example such as:

Pivot Table and Concatenate Columns

or this:

PIVOT in sql 2005