title: Split column over 2 columns and sum
body: <p>I have a table that looks like this  (Using SQL Server 2008 R2)</p>
  
  <pre><code>   [UNIQID] [int] IDENTITY(1,1) NOT NULL,
     [SaleDate] [smalldatetime] NOT NULL,
     [Invoice] [varchar](8) NOT NULL,
     [PDF File] [varchar](12) NULL,
     [Client] [tinyint] NOT NULL,
     [SaleStaff] [tinyint] NOT NULL,
     [Amount] [smallmoney] NOT NULL,
     [InvType] [tinyint] NOT NULL,
     [Paytype] [tinyint] NOT NULL,
     [remarks] [varchar](30) NULL
  </code></pre>
  
  <p><strong>The table have 9200 records in it</strong>
  I would like the Amount to be split over Paytype. I also managed to make it work, but it just seems very cumbersome.</p>
  
  <p>This code works it first inserts all of <code>paytype</code> 1 into a Temptable. Then it inserts all of <code>Paytype</code> 2</p>
  
  <pre><code>```
  {   {IF object_id('tempdb..#t') IS NOT NULL DROP TABLE #t
      CREATE TABLE #t
      ([UNIQID] [int] NOT NULL,
      [SaleDate] [smalldatetime] NOT NULL,
      [PDF File] [varchar](12) NULL,
      [SaleStaff] [tinyint] NOT NULL,
      [Paytype] [tinyint] NOT NULL,
      [xCash] varchar(8) NOT NULL,
      [Xcard] varchar(8) NOT NULL)
  
  
       insert into #t
       SELECT [UNIQID]
        ,[SaleDate]
        ,[invoice]
        ,[SaleStaff]
        ,[Paytype]
        ,[xcash] = convert(varchar(8),[Amount]) 
        ,[xCard] = ''
         FROM [dbo].[xxxSales]
         where (Paytype =1)
  
         insert into #t
         SELECT [UNIQID]
        ,[SaleDate]
        ,[Invoice]
        ,[SaleStaff]
        ,[Paytype]
        ,[xcash] =  ''
        ,[xCard] =convert(varchar(8),[Amount])
        FROM [dbo].[xxxSales]
         where (Paytype =2)
  
      Select max([UNIQID]) as id
      ,convert(varchar,[SaleDate],103) as saledate 
      ,max([PDF File]) as invoiceno 
      ,Max(dbo.fnStaff([SaleStaff])) as sid
      ,case PAyType when 1 then 'Cash'
                    When 2 then 'Card'
                    When 4 Then 'Order'
                    end as paytype 
      ,[xCash] as Cash
      ,[Xcard] as Card
      ,Sum(convert(Smallmoney,[xCash] ))OVER(partition BY PAyType)as CashTotal
      ,Sum(convert(Smallmoney,[xCaRD] ))OVER(partition BY PAyType)as CardTotal    
      from #t  
      where ([SaleDate] &gt; '2019-01-01') and ([SaleDate] &lt; '2019-01-12') 
      Group by  paytype,SaleDate,xCash,xcard  } ```
  </code></pre>
  
  <p>Last it selects from <code>temp table</code> any records between 2 dates</p>
  
  <p><strong>It gives this result</strong> </p>
  
  <pre><code> id saledate    invoiceno   sid paytype Cash    Card    CashTotal   CardTotal
  
  
   1. 1248    08/01/2019  I191334 Unknown Cash    100.00      335.00  0.00
   2. 1251    08/01/2019  I191337 Unknown Cash    15.00       335.00  0.00
   3. 1250    08/01/2019  I191336 Unknown Cash    80.00       335.00  0.00
   4. 1257    10/01/2019  I191346 Unknown Cash    100.00      335.00  0.00
   5. 1255    10/01/2019  I191343 Unknown Cash    40.00       335.00  0.00
   6. 1243    08/01/2019  I191327 Unknown Card        80.00   0.00    195.00
   7. 1252    10/01/2019  I191338 Unknown Card        100.00  0.00    195.00
   8. 1256    10/01/2019  I191345 Unknown Card        15.00   0.00    195.00
  </code></pre>
  
  <p>The code should eventually go into a Stored Procedure, where the dates come
  in as Parameters.</p>
  
  <p>Some requested sample data:</p>
  
  <pre><code>UNIQID SaleDate Invoice PDF File Client SaleStaff Amount InvType    Paytype remarks
  1241    2019-01-08 00:00:00 I191324 I191324.PDF 0   0   100.00  1   1   
  1242    2019-01-08 00:00:00 I191326 I191326.PDF 0   0   80.00   1   1   
  1243    2019-01-08 00:00:00 I191327 I191327.PDF 0   0   80.00   1   2   
  1244    2019-01-08 00:00:00 I191330 I191330.PDF 0   0   15.00   1   1   
  1245    2019-01-08 00:00:00 I191331 I191331.PDF 0   0   100.00  1   1   
  1246    2019-01-08 00:00:00 I191332 I191332.PDF 0   0   80.00   1   1   
  1248    2019-01-08 00:00:00 I191334 I191334.PDF 0   0   100.00  1   1   
  1249    2019-01-08 00:00:00 I191335 I191335.PDF 0   0   80.00   1   1   
  1250    2019-01-08 00:00:00 I191336 I191336.PDF 0   0   80.00   1   1   
  1251    2019-01-08 00:00:00 I191337 I191337.PDF 0   0   15.00   1   1   
  1252    2019-01-10 00:00:00 I191338 I191338.PDF 0   0   100.00  1   2   
  1253    2019-01-10 00:00:00 I191339 I191339.PDF 0   0   100.00  1   1   
  1255    2019-01-10 00:00:00 I191343 I191343.PDF 0   0   40.00   1   1   
  1256    2019-01-10 00:00:00 I191345 I191345.PDF 0   0   15.00   1   2   
  1257    2019-01-10 00:00:00 I191346 I191346.PDF 0   0   100.00  1   1   
  </code></pre>