• drew.allen - Tuesday, January 2, 2018 1:25 PM

    Yes.  Use GROUPING SETS.  If you want a more complete answer, post sample data and expected results as outlined in the first link in my signature.

    This would be much easier in a report tool such as SSRS.  Have you considered that?

    Drew

    OK, I am not getting the results so I will post some data.

    The end result should look similar to:
    fvendno fcompany POItem PORelsDate........TotStdCost
    001275 B&R Industried.............................. 1.00
    001275 B&R Industried.............................. 1.00 (total Row)
    002119 Roberson Tool................................ 2.00
    002119 Roberson Tool................................ 3.00
    002119 Roberson Tool................................ 5.00 (total Row)
    002302 Unique Automation.......................... 4.00 ( and so on).

    --===== If the test table already exists, drop it
      IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
       DROP TABLE #mytable

    --===== Create the test table with
    Create table #mytable(
        fvendno char(6),
        fcompany char(50),
        POItemKey char(12),
        PORelsdate datetime,
        totstdcost numeric(15,4),
        ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)

    --===== All Inserts into the IDENTITY column
        Set Identity_Insert #mytable ON
    --===== Insert the test data into the test table
        Insert into #mytable (fvendno, fcompany, POitemKey, PORelsDate, totstdcost, ID)
        Select '000027', 'Albany Steel', '123456 1', Cast('01/01/2018' as datetime), 1.00, 1 Union all
        Select '000027', 'Albany Steel', '123459 1', Cast('01/01/2018' as datetime), 2.00, 2 Union all
        Select '000060', 'Apple Rubber', '123455 1', Cast('01/01/2018' as datetime), 3.00, 3 Union all
        Select '000060', 'Apple Rubber', '123467 1', Cast('01/01/2018' as datetime), 4.00, 4 Union all
        Select '000080', 'Century Spring', '189456 1', Cast('01/01/2018' as datetime), 5.00, 5 Union all
        Select '000127', 'Everett Manufacturing', '223456 1', Cast('01/01/2018' as datetime), 6.00, 6

    --===== Set the identity insert back to normal
        Set Identity_Insert #mytable OFF

        Select * from #mytable