How to Change SQL select query As new table in SQL Server CE?

Question

This is my current SQL query:

SELECT 
    SUM(CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) >= 45)
             THEN tbl_invoices.pendingamount ELSE 0 END) AS morethan45,
    SUM(CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) < 45)
           THEN tbl_invoices.pendingamount ELSE 0 END) AS lessthan45
FROM 
    tbl_debit 
INNER JOIN 
    tbl_invoices ON tbl_debit.invoice = tbl_invoices.invoice  
WHERE 
    (tbl_invoices.state = - 1)

Output of above query is shown here:

morethan45    |    lessthan45
  750         |       710

And I want to create a new table like below. Is it possible to create something new like below

Column 1      | Column 2
morethan45    |    750
lessthan45    |    710

Answer 1

Same query can be altered a bit to get the result.

Instead of aggregating the pendingamount based on condition you can add a new column to define the range and use it in Group by

SELECT CASE
         WHEN Datediff(day, tbl_debit.purchasedate, Getdate()) >= 45 THEN 'morethan45'
         ELSE 'lessthan45'
       END                             AS [Column 1],
       Sum(tbl_invoices.pendingamount) AS [Column 2]
FROM   tbl_debit
       INNER JOIN tbl_invoices
               ON tbl_debit.invoice = tbl_invoices.invoice
WHERE  tbl_invoices.state = -1
GROUP  BY CASE
            WHEN ( Datediff(day, tbl_debit.purchasedate, Getdate()) >= 45 ) THEN 'morethan45'
            ELSE 'lessthan45'
          END