Pentaho migration to SQL Server Pivot table with merging and grouping

Question

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. :(

score 2 · Accepted Answer · edited Jul 20 '17 at 02:09

Here is solution which works great to "unpivot 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)

Awesome answer @Luis, Thanks! This is what I was looking for. BTW welcome to the StackOverFlow! — ChikuMiku, Sep 20 '16 at 14:25

Pentaho migration to SQL Server Pivot table with merging and grouping

1 Answers1