ChikuMiku ChikuMiku - 2 months ago 5
SQL Question

Pentaho migration to SQL Server Pivot table with merging and grouping

I have a Pentaho job and in that, we are doing some Normalization of the table with pivot table. I want to change that into SQL server.
My SQL server has following data:

Rep_Create_Date InventoryID Beg_Bal_Pennies Beg_Bal_Nickels Beg_Bal_Dimes Beg_Bal_Quaters Beg_Bal_Halves Beg_Bal_Dollar Beg_Bal_Loose Beg_Bal_Ones Beg_Bal_Twos Beg_Bal_Fives Beg_Bal_Tens Beg_Bal_Twenties Beg_Bal_Fifties Beg_Bal_Hundreds Inb_Bulk_Pennies Inb_Bulk_Nickels Inb_Bulk_Dimes Inb_Bulk_Quaters Inb_Bulk_Halves Inb_Bulk_Dollar Inb_Bulk_Loose Inb_Bulk_Ones Inb_Bulk_Twos Inb_Bulk_Fives Inb_Bulk_Tens Inb_Bulk_Twenties Inb_Bulk_Fifties Inb_Bulk_Hundreds Out_Bulk_Pennies Out_Bulk_Nickels Out_Bulk_Dimes Out_Bulk_Quaters Out_Bulk_Halves Out_Bulk_Dollar Out_Bulk_Loose Out_Bulk_Ones Out_Bulk_Twos Out_Bulk_Fives Out_Bulk_Tens Out_Bulk_Twenties Out_Bulk_Fifties Out_Bulk_Hundreds Out_Pack_Pennies Out_Pack_Nickels Out_Pack_Dimes Out_Pack_Quaters Out_Pack_Halves Out_Pack_Dollar Out_Pack_Loose Out_Pack_Ones Out_Pack_Twos Out_Pack_Fives Out_Pack_Tens Out_Pack_Twenties Out_Pack_Fifties Out_Pack_Hundreds Inv_Tran_Pennies Inv_Tran_Nickels Inv_Tran_Dimes Inv_Tran_Quaters Inv_Tran_Halves Inv_Tran_Dollar Inv_Tran_Loose Inv_Tran_Ones Inv_Tran_Twos Inv_Tran_Fives Inv_Tran_Tens Inv_Tran_Twenties Inv_Tran_Fifties Inv_Tran_Hundreds Inv_Adj_Pennies Inv_Adj_Nickels Inv_Adj_Dimes Inv_Adj_Quaters Inv_Adj_Halves Inv_Adj_Dollar Inv_Adj_Loose Inv_Adj_Ones Inv_Adj_Twos Inv_Adj_Fives Inv_Adj_Tens Inv_Adj_Twenties Inv_Adj_Fifties Inv_Adj_Hundreds Phy_Bal_Pennies Phy_Bal_Nickels Phy_Bal_Dimes Phy_Bal_Quaters Phy_Bal_Halves Phy_Bal_Dollar Phy_Bal_Loose Phy_Bal_Ones Phy_Bal_Twos Phy_Bal_Fives Phy_Bal_Tens Phy_Bal_Twenties Phy_Bal_Fifties Phy_Bal_Hundreds
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20160907 014 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 354060.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 41900.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 00.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 395960.00 0.00 0.00
20160907 018 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 919600.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 347060.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 00.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 304000.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 962660.00 0.00 0.00
20160907 050 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 970300.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 13860.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 00.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 130000.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 85416000 0.00 0.00
20160907 073 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1517360.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 494860.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 00.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 606000.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1406220.00 0.00 0.00


Only four records are there in this table say
tableSourcePentaho
. Pentaho is doing normalization of the table with following schema, where we are changing fields into newNames and grouping them as per
denoms
:

Fieldname Type new Field
-------------------------------------------------------
Beg_Bal_Pennies 1 Beg_Bal
Beg_Bal_Nickels 5 Beg_Bal
Beg_Bal_Dimes 10 Beg_Bal
Beg_Bal_Quaters 25 Beg_Bal
Beg_Bal_Halves 50 Beg_Bal
Beg_Bal_Dollar 101 Beg_Bal
Beg_Bal_Loose 98 Beg_Bal
Beg_Bal_Ones 100 Beg_Bal
Beg_Bal_Twos 200 Beg_Bal
Beg_Bal_Fives 500 Beg_Bal
Beg_Bal_Tens 1000 Beg_Bal
Beg_Bal_Twenties 2000 Beg_Bal
Beg_Bal_Fifties 5000 Beg_Bal
Beg_Bal_Hundreds 10000 Beg_Bal
Inb_Bulk_Pennies 1 Cash_In
Inb_Bulk_Nickels 5 Cash_In
Inb_Bulk_Dimes 10 Cash_In
Inb_Bulk_Quaters 25 Cash_In
Inb_Bulk_Halves 50 Cash_In
Inb_Bulk_Dollar 101 Cash_In
Inb_Bulk_Loose 98 Cash_In
Inb_Bulk_Ones 100 Cash_In
Inb_Bulk_Twos 200 Cash_In
Inb_Bulk_Fives 500 Cash_In
Inb_Bulk_Tens 1000 Cash_In
Inb_Bulk_Twenties 2000 Cash_In
Inb_Bulk_Fifties 5000 Cash_In
Inb_Bulk_Hundreds 10000 Cash_In
Out_Pack_Pennies 1 Cash_Out
Out_Pack_Nickels 5 Cash_Out
Out_Pack_Dimes 10 Cash_Out
Out_Pack_Quaters 25 Cash_Out
Out_Pack_Halves 50 Cash_Out
Out_Pack_Dollar 101 Cash_Out
Out_Pack_Loose 98 Cash_Out
Out_Pack_Ones 100 Cash_Out
Out_Pack_Twos 200 Cash_Out
Out_Pack_Fives 500 Cash_Out
Out_Pack_Tens 1000 Cash_Out
Out_Pack_Twenties 2000 Cash_Out
Out_Pack_Fifties 5000 Cash_Out
Out_Pack_Hundreds 10000 Cash_Out
Inv_Adj_Pennies 1 Inv_Adj
Inv_Adj_Nickels 5 Inv_Adj
Inv_Adj_Dimes 10 Inv_Adj
Inv_Adj_Quaters 25 Inv_Adj
Inv_Adj_Halves 50 Inv_Adj
Inv_Adj_Dollar 101 Inv_Adj
Inv_Adj_Loose 98 Inv_Adj
Inv_Adj_Ones 100 Inv_Adj
Inv_Adj_Twos 200 Inv_Adj
Inv_Adj_Fives 500 Inv_Adj
Inv_Adj_Tens 1000 Inv_Adj
Inv_Adj_Twenties 2000 Inv_Adj
Inv_Adj_Fifties 5000 Inv_Adj
Inv_Adj_Hundreds 10000 Inv_Adj
Phy_Bal_Pennies 1 Phy_Bal
Phy_Bal_Nickels 5 Phy_Bal
Phy_Bal_Dimes 10 Phy_Bal
Phy_Bal_Quaters 25 Phy_Bal
Phy_Bal_Halves 50 Phy_Bal
Phy_Bal_Dollar 101 Phy_Bal
Phy_Bal_Loose 98 Phy_Bal
Phy_Bal_Ones 100 Phy_Bal
Phy_Bal_Twos 200 Phy_Bal
Phy_Bal_Fives 500 Phy_Bal
Phy_Bal_Tens 1000 Phy_Bal
Phy_Bal_Twenties 2000 Phy_Bal
Phy_Bal_Fifties 5000 Phy_Bal
Phy_Bal_Hundreds 10000 Phy_Bal
Inv_Tran_Pennies 1 Ship_In
Inv_Tran_Nickels 5 Ship_In
Inv_Tran_Dimes 10 Ship_In
Inv_Tran_Quaters 25 Ship_In
Inv_Tran_Halves 50 Ship_In
Inv_Tran_Dollar 101 Ship_In
Inv_Tran_Loose 98 Ship_In
Inv_Tran_Ones 100 Ship_In
Inv_Tran_Twos 200 Ship_In
Inv_Tran_Fives 500 Ship_In
Inv_Tran_Tens 1000 Ship_In
Inv_Tran_Twenties 2000 Ship_In
Inv_Tran_Fifties 5000 Ship_In
Inv_Tran_Hundreds 10000 Ship_In
Out_Bulk_Pennies 1 Ship_Out
Out_Bulk_Nickels 5 Ship_Out
Out_Bulk_Dimes 10 Ship_Out
Out_Bulk_Quaters 25 Ship_Out
Out_Bulk_Halves 50 Ship_Out
Out_Bulk_Dollar 101 Ship_Out
Out_Bulk_Loose 98 Ship_Out
Out_Bulk_Ones 100 Ship_Out
Out_Bulk_Twos 200 Ship_Out
Out_Bulk_Fives 500 Ship_Out
Out_Bulk_Tens 1000 Ship_Out
Out_Bulk_Twenties 2000 Ship_Out
Out_Bulk_Fifties 5000 Ship_Out
Out_Bulk_Hundreds 10000 Ship_Out


We are not doing any
aggregation
for this, so I want to convert/pivot table into
targetTable
where result of the query must be following:

InventoryID, Rep_Create_Date, Denom, Beg_Bal, Phy_Bal, Cash_In, Cash_Out, Ship_In, Ship_Out, Inv_Adj
014 20160907 1 000 000 000 000 000 000 000
014 20160907 5 000 000 000 000 000 000 000
014 20160907 10 000 000 000 000 000 000 000
014 20160907 25 000 000 000 000 000 000 000
014 20160907 50 000 000 000 000 000 000 000
014 20160907 101 000 000 000 000 000 000 000
014 20160907 98 000 000 000 000 000 000 000
014 20160907 100 000 000 000 000 000 000 000
014 20160907 200 000 000 000 000 000 000 000
014 20160907 500 000 000 000 000 000 000 000
014 20160907 1000 000 000 000 000 000 000 000
014 20160907 2000 35406000 39596000 4190000 000 000 000 000
014 20160907 5000 000 000 000 000 000 000 000
014 20160907 10000 000 000 000 000 000 000 000
018 20160907 1 000 000 000 000 000 000 000
018 20160907 5 000 000 000 000 000 000 000
018 20160907 10 000 000 000 000 000 000 000
018 20160907 25 000 000 000 000 000 000 000
018 20160907 50 000 000 000 000 000 000 000
018 20160907 101 000 000 000 000 000 000 000
018 20160907 98 000 000 000 000 000 000 000
018 20160907 100 000 000 000 000 000 000 000
018 20160907 200 000 000 000 000 000 000 000
018 20160907 500 000 000 000 000 000 000 000
018 20160907 1000 000 000 000 000 000 000 000
018 20160907 2000 91960000 96266000 34706000 30400000 000 000 000
018 20160907 5000 000 000 000 000 000 000 000
018 20160907 10000 000 000 000 000 000 000 000
050 20160907 1 000 000 000 000 000 000 000
050 20160907 5 000 000 000 000 000 000 000
050 20160907 10 000 000 000 000 000 000 000
050 20160907 25 000 000 000 000 000 000 000
050 20160907 50 000 000 000 000 000 000 000
050 20160907 101 000 000 000 000 000 000 000
050 20160907 98 000 000 000 000 000 000 000
050 20160907 100 000 000 000 000 000 000 000
050 20160907 200 000 000 000 000 000 000 000
050 20160907 500 000 000 000 000 000 000 000
050 20160907 1000 000 000 000 000 000 000 000
050 20160907 2000 97030000 85416000 1386000 13000000 000 000 000
050 20160907 5000 000 000 000 000 000 000 000
050 20160907 10000 000 000 000 000 000 000 000
073 20160907 1 000 000 000 000 000 000 000
073 20160907 5 000 000 000 000 000 000 000
073 20160907 10 000 000 000 000 000 000 000
073 20160907 25 000 000 000 000 000 000 000
073 20160907 50 000 000 000 000 000 000 000
073 20160907 101 000 000 000 000 000 000 000
073 20160907 98 000 000 000 000 000 000 000
073 20160907 100 000 000 000 000 000 000 000
073 20160907 200 000 000 000 000 000 000 000
073 20160907 500 000 000 000 000 000 000 000
073 20160907 1000 000 000 000 000 000 000 000
073 20160907 2000 151736000 140622000 49486000 60600000 000 000 000
073 20160907 5000 000 000 000 000 000 000 000
073 20160907 10000 000 000 000 000 000 000 000


I have tried many things like Transpose rows and columns with no aggregate and Pivot rows to columns without aggregate but not getting exactly what I am looking for. Please help me to get rid off this. I am newbiew in Databases.

PS: Sorry for long question. :(

Answer

Here is solution which works great to unipivot multiple columns at a time. It's preceded by the creation of sample data, so look for the solution after the comment.

IF OBJECT_ID( 'TestData') IS NOT NULL
    DROP TABLE TestData;

CREATE TABLE TestData(
   Rep_Create_Date   DATE 
  ,InventoryID       INTEGER
  ,Beg_Bal_Pennies   NUMERIC(10,2)
  ,Beg_Bal_Nickels   NUMERIC(10,2)
  ,Beg_Bal_Dimes     NUMERIC(10,2)
  ,Beg_Bal_Quaters   NUMERIC(10,2)
  ,Beg_Bal_Halves    NUMERIC(10,2)
  ,Beg_Bal_Dollar    NUMERIC(10,2)
  ,Beg_Bal_Loose     NUMERIC(10,2)
  ,Beg_Bal_Ones      NUMERIC(10,2)
  ,Beg_Bal_Twos      NUMERIC(10,2)
  ,Beg_Bal_Fives     NUMERIC(10,2)
  ,Beg_Bal_Tens      NUMERIC(10,2)
  ,Beg_Bal_Twenties  NUMERIC(10,2)
  ,Beg_Bal_Fifties   NUMERIC(10,2)
  ,Beg_Bal_Hundreds  NUMERIC(10,2)
  ,Inb_Bulk_Pennies  NUMERIC(10,2)
  ,Inb_Bulk_Nickels  NUMERIC(10,2)
  ,Inb_Bulk_Dimes    NUMERIC(10,2)
  ,Inb_Bulk_Quaters  NUMERIC(10,2)
  ,Inb_Bulk_Halves   NUMERIC(10,2)
  ,Inb_Bulk_Dollar   NUMERIC(10,2)
  ,Inb_Bulk_Loose    NUMERIC(10,2)
  ,Inb_Bulk_Ones     NUMERIC(10,2)
  ,Inb_Bulk_Twos     NUMERIC(10,2)
  ,Inb_Bulk_Fives    NUMERIC(10,2)
  ,Inb_Bulk_Tens     NUMERIC(10,2)
  ,Inb_Bulk_Twenties NUMERIC(10,2)
  ,Inb_Bulk_Fifties  NUMERIC(10,2)
  ,Inb_Bulk_Hundreds NUMERIC(10,2)
  ,Out_Bulk_Pennies  NUMERIC(10,2)
  ,Out_Bulk_Nickels  NUMERIC(10,2)
  ,Out_Bulk_Dimes    NUMERIC(10,2)
  ,Out_Bulk_Quaters  NUMERIC(10,2)
  ,Out_Bulk_Halves   NUMERIC(10,2)
  ,Out_Bulk_Dollar   NUMERIC(10,2)
  ,Out_Bulk_Loose    NUMERIC(10,2)
  ,Out_Bulk_Ones     NUMERIC(10,2)
  ,Out_Bulk_Twos     NUMERIC(10,2)
  ,Out_Bulk_Fives    NUMERIC(10,2)
  ,Out_Bulk_Tens     NUMERIC(10,2)
  ,Out_Bulk_Twenties NUMERIC(10,2)
  ,Out_Bulk_Fifties  NUMERIC(10,2)
  ,Out_Bulk_Hundreds NUMERIC(10,2)
  ,Out_Pack_Pennies  NUMERIC(10,2)
  ,Out_Pack_Nickels  NUMERIC(10,2)
  ,Out_Pack_Dimes    NUMERIC(10,2)
  ,Out_Pack_Quaters  NUMERIC(10,2)
  ,Out_Pack_Halves   NUMERIC(10,2)
  ,Out_Pack_Dollar   NUMERIC(10,2)
  ,Out_Pack_Loose    NUMERIC(10,2)
  ,Out_Pack_Ones     NUMERIC(10,2)
  ,Out_Pack_Twos     NUMERIC(10,2)
  ,Out_Pack_Fives    NUMERIC(10,2)
  ,Out_Pack_Tens     NUMERIC(10,2)
  ,Out_Pack_Twenties NUMERIC(10,2)
  ,Out_Pack_Fifties  NUMERIC(10,2)
  ,Out_Pack_Hundreds NUMERIC(10,2)
  ,Inv_Tran_Pennies  NUMERIC(10,2)
  ,Inv_Tran_Nickels  NUMERIC(10,2)
  ,Inv_Tran_Dimes    NUMERIC(10,2)
  ,Inv_Tran_Quaters  NUMERIC(10,2)
  ,Inv_Tran_Halves   NUMERIC(10,2)
  ,Inv_Tran_Dollar   NUMERIC(10,2)
  ,Inv_Tran_Loose    NUMERIC(10,2)
  ,Inv_Tran_Ones     NUMERIC(10,2)
  ,Inv_Tran_Twos     NUMERIC(10,2)
  ,Inv_Tran_Fives    NUMERIC(10,2)
  ,Inv_Tran_Tens     NUMERIC(10,2)
  ,Inv_Tran_Twenties NUMERIC(10,2)
  ,Inv_Tran_Fifties  NUMERIC(10,2)
  ,Inv_Tran_Hundreds NUMERIC(10,2)
  ,Inv_Adj_Pennies   NUMERIC(10,2)
  ,Inv_Adj_Nickels   NUMERIC(10,2)
  ,Inv_Adj_Dimes     NUMERIC(10,2)
  ,Inv_Adj_Quaters   NUMERIC(10,2)
  ,Inv_Adj_Halves    NUMERIC(10,2)
  ,Inv_Adj_Dollar    NUMERIC(10,2)
  ,Inv_Adj_Loose     NUMERIC(10,2)
  ,Inv_Adj_Ones      NUMERIC(10,2)
  ,Inv_Adj_Twos      NUMERIC(10,2)
  ,Inv_Adj_Fives     NUMERIC(10,2)
  ,Inv_Adj_Tens      NUMERIC(10,2)
  ,Inv_Adj_Twenties  NUMERIC(10,2)
  ,Inv_Adj_Fifties   NUMERIC(10,2)
  ,Inv_Adj_Hundreds  NUMERIC(10,2)
  ,Phy_Bal_Pennies   NUMERIC(10,2)
  ,Phy_Bal_Nickels   NUMERIC(10,2)
  ,Phy_Bal_Dimes     NUMERIC(10,2)
  ,Phy_Bal_Quaters   NUMERIC(10,2)
  ,Phy_Bal_Halves    NUMERIC(10,2)
  ,Phy_Bal_Dollar    NUMERIC(10,2)
  ,Phy_Bal_Loose     NUMERIC(10,2)
  ,Phy_Bal_Ones      NUMERIC(10,2)
  ,Phy_Bal_Twos      NUMERIC(10,2)
  ,Phy_Bal_Fives     NUMERIC(10,2)
  ,Phy_Bal_Tens      NUMERIC(10,2)
  ,Phy_Bal_Twenties  NUMERIC(10,2)
  ,Phy_Bal_Fifties   NUMERIC(10,2)
  ,Phy_Bal_Hundreds  NUMERIC(10,2)
);
INSERT INTO TestData(Rep_Create_Date,InventoryID,Beg_Bal_Pennies,Beg_Bal_Nickels,Beg_Bal_Dimes,Beg_Bal_Quaters,Beg_Bal_Halves,Beg_Bal_Dollar,Beg_Bal_Loose,Beg_Bal_Ones,Beg_Bal_Twos,Beg_Bal_Fives,Beg_Bal_Tens,Beg_Bal_Twenties,Beg_Bal_Fifties,Beg_Bal_Hundreds,Inb_Bulk_Pennies,Inb_Bulk_Nickels,Inb_Bulk_Dimes,Inb_Bulk_Quaters,Inb_Bulk_Halves,Inb_Bulk_Dollar,Inb_Bulk_Loose,Inb_Bulk_Ones,Inb_Bulk_Twos,Inb_Bulk_Fives,Inb_Bulk_Tens,Inb_Bulk_Twenties,Inb_Bulk_Fifties,Inb_Bulk_Hundreds,Out_Bulk_Pennies,Out_Bulk_Nickels,Out_Bulk_Dimes,Out_Bulk_Quaters,Out_Bulk_Halves,Out_Bulk_Dollar,Out_Bulk_Loose,Out_Bulk_Ones,Out_Bulk_Twos,Out_Bulk_Fives,Out_Bulk_Tens,Out_Bulk_Twenties,Out_Bulk_Fifties,Out_Bulk_Hundreds,Out_Pack_Pennies,Out_Pack_Nickels,Out_Pack_Dimes,Out_Pack_Quaters,Out_Pack_Halves,Out_Pack_Dollar,Out_Pack_Loose,Out_Pack_Ones,Out_Pack_Twos,Out_Pack_Fives,Out_Pack_Tens,Out_Pack_Twenties,Out_Pack_Fifties,Out_Pack_Hundreds,Inv_Tran_Pennies,Inv_Tran_Nickels,Inv_Tran_Dimes,Inv_Tran_Quaters,Inv_Tran_Halves,Inv_Tran_Dollar,Inv_Tran_Loose,Inv_Tran_Ones,Inv_Tran_Twos,Inv_Tran_Fives,Inv_Tran_Tens,Inv_Tran_Twenties,Inv_Tran_Fifties,Inv_Tran_Hundreds,Inv_Adj_Pennies,Inv_Adj_Nickels,Inv_Adj_Dimes,Inv_Adj_Quaters,Inv_Adj_Halves,Inv_Adj_Dollar,Inv_Adj_Loose,Inv_Adj_Ones,Inv_Adj_Twos,Inv_Adj_Fives,Inv_Adj_Tens,Inv_Adj_Twenties,Inv_Adj_Fifties,Inv_Adj_Hundreds,Phy_Bal_Pennies,Phy_Bal_Nickels,Phy_Bal_Dimes,Phy_Bal_Quaters,Phy_Bal_Halves,Phy_Bal_Dollar,Phy_Bal_Loose,Phy_Bal_Ones,Phy_Bal_Twos,Phy_Bal_Fives,Phy_Bal_Tens,Phy_Bal_Twenties,Phy_Bal_Fifties,Phy_Bal_Hundreds) VALUES ('20160907',014,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,354060.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,41900.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,00.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,395960.00,0.00,0.00);
INSERT INTO TestData(Rep_Create_Date,InventoryID,Beg_Bal_Pennies,Beg_Bal_Nickels,Beg_Bal_Dimes,Beg_Bal_Quaters,Beg_Bal_Halves,Beg_Bal_Dollar,Beg_Bal_Loose,Beg_Bal_Ones,Beg_Bal_Twos,Beg_Bal_Fives,Beg_Bal_Tens,Beg_Bal_Twenties,Beg_Bal_Fifties,Beg_Bal_Hundreds,Inb_Bulk_Pennies,Inb_Bulk_Nickels,Inb_Bulk_Dimes,Inb_Bulk_Quaters,Inb_Bulk_Halves,Inb_Bulk_Dollar,Inb_Bulk_Loose,Inb_Bulk_Ones,Inb_Bulk_Twos,Inb_Bulk_Fives,Inb_Bulk_Tens,Inb_Bulk_Twenties,Inb_Bulk_Fifties,Inb_Bulk_Hundreds,Out_Bulk_Pennies,Out_Bulk_Nickels,Out_Bulk_Dimes,Out_Bulk_Quaters,Out_Bulk_Halves,Out_Bulk_Dollar,Out_Bulk_Loose,Out_Bulk_Ones,Out_Bulk_Twos,Out_Bulk_Fives,Out_Bulk_Tens,Out_Bulk_Twenties,Out_Bulk_Fifties,Out_Bulk_Hundreds,Out_Pack_Pennies,Out_Pack_Nickels,Out_Pack_Dimes,Out_Pack_Quaters,Out_Pack_Halves,Out_Pack_Dollar,Out_Pack_Loose,Out_Pack_Ones,Out_Pack_Twos,Out_Pack_Fives,Out_Pack_Tens,Out_Pack_Twenties,Out_Pack_Fifties,Out_Pack_Hundreds,Inv_Tran_Pennies,Inv_Tran_Nickels,Inv_Tran_Dimes,Inv_Tran_Quaters,Inv_Tran_Halves,Inv_Tran_Dollar,Inv_Tran_Loose,Inv_Tran_Ones,Inv_Tran_Twos,Inv_Tran_Fives,Inv_Tran_Tens,Inv_Tran_Twenties,Inv_Tran_Fifties,Inv_Tran_Hundreds,Inv_Adj_Pennies,Inv_Adj_Nickels,Inv_Adj_Dimes,Inv_Adj_Quaters,Inv_Adj_Halves,Inv_Adj_Dollar,Inv_Adj_Loose,Inv_Adj_Ones,Inv_Adj_Twos,Inv_Adj_Fives,Inv_Adj_Tens,Inv_Adj_Twenties,Inv_Adj_Fifties,Inv_Adj_Hundreds,Phy_Bal_Pennies,Phy_Bal_Nickels,Phy_Bal_Dimes,Phy_Bal_Quaters,Phy_Bal_Halves,Phy_Bal_Dollar,Phy_Bal_Loose,Phy_Bal_Ones,Phy_Bal_Twos,Phy_Bal_Fives,Phy_Bal_Tens,Phy_Bal_Twenties,Phy_Bal_Fifties,Phy_Bal_Hundreds) VALUES ('20160907',018,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,919600.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,347060.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,00.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,304000.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,962660.00,0.00,0.00);
INSERT INTO TestData(Rep_Create_Date,InventoryID,Beg_Bal_Pennies,Beg_Bal_Nickels,Beg_Bal_Dimes,Beg_Bal_Quaters,Beg_Bal_Halves,Beg_Bal_Dollar,Beg_Bal_Loose,Beg_Bal_Ones,Beg_Bal_Twos,Beg_Bal_Fives,Beg_Bal_Tens,Beg_Bal_Twenties,Beg_Bal_Fifties,Beg_Bal_Hundreds,Inb_Bulk_Pennies,Inb_Bulk_Nickels,Inb_Bulk_Dimes,Inb_Bulk_Quaters,Inb_Bulk_Halves,Inb_Bulk_Dollar,Inb_Bulk_Loose,Inb_Bulk_Ones,Inb_Bulk_Twos,Inb_Bulk_Fives,Inb_Bulk_Tens,Inb_Bulk_Twenties,Inb_Bulk_Fifties,Inb_Bulk_Hundreds,Out_Bulk_Pennies,Out_Bulk_Nickels,Out_Bulk_Dimes,Out_Bulk_Quaters,Out_Bulk_Halves,Out_Bulk_Dollar,Out_Bulk_Loose,Out_Bulk_Ones,Out_Bulk_Twos,Out_Bulk_Fives,Out_Bulk_Tens,Out_Bulk_Twenties,Out_Bulk_Fifties,Out_Bulk_Hundreds,Out_Pack_Pennies,Out_Pack_Nickels,Out_Pack_Dimes,Out_Pack_Quaters,Out_Pack_Halves,Out_Pack_Dollar,Out_Pack_Loose,Out_Pack_Ones,Out_Pack_Twos,Out_Pack_Fives,Out_Pack_Tens,Out_Pack_Twenties,Out_Pack_Fifties,Out_Pack_Hundreds,Inv_Tran_Pennies,Inv_Tran_Nickels,Inv_Tran_Dimes,Inv_Tran_Quaters,Inv_Tran_Halves,Inv_Tran_Dollar,Inv_Tran_Loose,Inv_Tran_Ones,Inv_Tran_Twos,Inv_Tran_Fives,Inv_Tran_Tens,Inv_Tran_Twenties,Inv_Tran_Fifties,Inv_Tran_Hundreds,Inv_Adj_Pennies,Inv_Adj_Nickels,Inv_Adj_Dimes,Inv_Adj_Quaters,Inv_Adj_Halves,Inv_Adj_Dollar,Inv_Adj_Loose,Inv_Adj_Ones,Inv_Adj_Twos,Inv_Adj_Fives,Inv_Adj_Tens,Inv_Adj_Twenties,Inv_Adj_Fifties,Inv_Adj_Hundreds,Phy_Bal_Pennies,Phy_Bal_Nickels,Phy_Bal_Dimes,Phy_Bal_Quaters,Phy_Bal_Halves,Phy_Bal_Dollar,Phy_Bal_Loose,Phy_Bal_Ones,Phy_Bal_Twos,Phy_Bal_Fives,Phy_Bal_Tens,Phy_Bal_Twenties,Phy_Bal_Fifties,Phy_Bal_Hundreds) VALUES ('20160907',050,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,970300.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,13860.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,00.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,130000.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,85416000,0.00,0.00);
INSERT INTO TestData(Rep_Create_Date,InventoryID,Beg_Bal_Pennies,Beg_Bal_Nickels,Beg_Bal_Dimes,Beg_Bal_Quaters,Beg_Bal_Halves,Beg_Bal_Dollar,Beg_Bal_Loose,Beg_Bal_Ones,Beg_Bal_Twos,Beg_Bal_Fives,Beg_Bal_Tens,Beg_Bal_Twenties,Beg_Bal_Fifties,Beg_Bal_Hundreds,Inb_Bulk_Pennies,Inb_Bulk_Nickels,Inb_Bulk_Dimes,Inb_Bulk_Quaters,Inb_Bulk_Halves,Inb_Bulk_Dollar,Inb_Bulk_Loose,Inb_Bulk_Ones,Inb_Bulk_Twos,Inb_Bulk_Fives,Inb_Bulk_Tens,Inb_Bulk_Twenties,Inb_Bulk_Fifties,Inb_Bulk_Hundreds,Out_Bulk_Pennies,Out_Bulk_Nickels,Out_Bulk_Dimes,Out_Bulk_Quaters,Out_Bulk_Halves,Out_Bulk_Dollar,Out_Bulk_Loose,Out_Bulk_Ones,Out_Bulk_Twos,Out_Bulk_Fives,Out_Bulk_Tens,Out_Bulk_Twenties,Out_Bulk_Fifties,Out_Bulk_Hundreds,Out_Pack_Pennies,Out_Pack_Nickels,Out_Pack_Dimes,Out_Pack_Quaters,Out_Pack_Halves,Out_Pack_Dollar,Out_Pack_Loose,Out_Pack_Ones,Out_Pack_Twos,Out_Pack_Fives,Out_Pack_Tens,Out_Pack_Twenties,Out_Pack_Fifties,Out_Pack_Hundreds,Inv_Tran_Pennies,Inv_Tran_Nickels,Inv_Tran_Dimes,Inv_Tran_Quaters,Inv_Tran_Halves,Inv_Tran_Dollar,Inv_Tran_Loose,Inv_Tran_Ones,Inv_Tran_Twos,Inv_Tran_Fives,Inv_Tran_Tens,Inv_Tran_Twenties,Inv_Tran_Fifties,Inv_Tran_Hundreds,Inv_Adj_Pennies,Inv_Adj_Nickels,Inv_Adj_Dimes,Inv_Adj_Quaters,Inv_Adj_Halves,Inv_Adj_Dollar,Inv_Adj_Loose,Inv_Adj_Ones,Inv_Adj_Twos,Inv_Adj_Fives,Inv_Adj_Tens,Inv_Adj_Twenties,Inv_Adj_Fifties,Inv_Adj_Hundreds,Phy_Bal_Pennies,Phy_Bal_Nickels,Phy_Bal_Dimes,Phy_Bal_Quaters,Phy_Bal_Halves,Phy_Bal_Dollar,Phy_Bal_Loose,Phy_Bal_Ones,Phy_Bal_Twos,Phy_Bal_Fives,Phy_Bal_Tens,Phy_Bal_Twenties,Phy_Bal_Fifties,Phy_Bal_Hundreds) VALUES ('20160907',073,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,1517360.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,494860.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,00.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,606000.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,1406220.00,0.00,0.00);


--This is the actual solution
SELECT td.InventoryID
    ,td.Rep_Create_Date
    ,up.Denom
    ,up.Beg_Bal
    ,up.Phy_Bal
    ,up.Cash_In
    ,up.Cash_Out
    ,up.Ship_In
    ,up.Ship_Out
    ,up.Inv_Adj
FROM TestData td
CROSS APPLY (VALUES (1    , Beg_Bal_Pennies   , Inb_Bulk_Pennies     , Out_Pack_Pennies   , Inv_Adj_Pennies  , Phy_Bal_Pennies , Inv_Tran_Pennies , Out_Bulk_Pennies ),
                    (5    , Beg_Bal_Nickels   , Inb_Bulk_Nickels     , Out_Pack_Nickels   , Inv_Adj_Nickels  , Phy_Bal_Nickels , Inv_Tran_Nickels , Out_Bulk_Nickels ),
                    (10   , Beg_Bal_Dimes     , Inb_Bulk_Dimes       , Out_Pack_Dimes     , Inv_Adj_Dimes    , Phy_Bal_Dimes   , Inv_Tran_Dimes   , Out_Bulk_Dimes   ),
                    (25   , Beg_Bal_Quaters   , Inb_Bulk_Quaters     , Out_Pack_Quaters   , Inv_Adj_Quaters  , Phy_Bal_Quaters , Inv_Tran_Quaters , Out_Bulk_Quaters ),
                    (50   , Beg_Bal_Halves    , Inb_Bulk_Halves      , Out_Pack_Halves    , Inv_Adj_Halves   , Phy_Bal_Halves  , Inv_Tran_Halves  , Out_Bulk_Halves  ),
                    (101  , Beg_Bal_Dollar    , Inb_Bulk_Dollar      , Out_Pack_Dollar    , Inv_Adj_Dollar   , Phy_Bal_Dollar  , Inv_Tran_Dollar  , Out_Bulk_Dollar  ),
                    (98   , Beg_Bal_Loose     , Inb_Bulk_Loose       , Out_Pack_Loose     , Inv_Adj_Loose    , Phy_Bal_Loose   , Inv_Tran_Loose   , Out_Bulk_Loose   ),
                    (100  , Beg_Bal_Ones      , Inb_Bulk_Ones        , Out_Pack_Ones      , Inv_Adj_Ones     , Phy_Bal_Ones    , Inv_Tran_Ones    , Out_Bulk_Ones    ),
                    (200  , Beg_Bal_Twos      , Inb_Bulk_Twos        , Out_Pack_Twos      , Inv_Adj_Twos     , Phy_Bal_Twos    , Inv_Tran_Twos    , Out_Bulk_Twos    ),
                    (500  , Beg_Bal_Fives     , Inb_Bulk_Fives       , Out_Pack_Fives     , Inv_Adj_Fives    , Phy_Bal_Fives   , Inv_Tran_Fives   , Out_Bulk_Fives   ),
                    (1000 , Beg_Bal_Tens      , Inb_Bulk_Tens        , Out_Pack_Tens      , Inv_Adj_Tens     , Phy_Bal_Tens    , Inv_Tran_Tens    , Out_Bulk_Tens    ),
                    (2000 , Beg_Bal_Twenties  , Inb_Bulk_Twenties    , Out_Pack_Twenties  , Inv_Adj_Twenties , Phy_Bal_Twenties, Inv_Tran_Twenties, Out_Bulk_Twenties),
                    (5000 , Beg_Bal_Fifties   , Inb_Bulk_Fifties     , Out_Pack_Fifties   , Inv_Adj_Fifties  , Phy_Bal_Fifties , Inv_Tran_Fifties , Out_Bulk_Fifties ),
                    (10000, Beg_Bal_Hundreds  , Inb_Bulk_Hundreds    , Out_Pack_Hundreds  , Inv_Adj_Hundreds , Phy_Bal_Hundreds, Inv_Tran_Hundreds, Out_Bulk_Hundreds)) up(Denom, Beg_Bal, Phy_Bal, Cash_In, Cash_Out, Ship_In, Ship_Out, Inv_Adj)