0

I currently have the following SQL server query:

SELECT * FROM 
(
SELECT CustomerID,LEFT(Time,4) Month,SUM(Amount) Amount FROM Payment
Group by CustomerID,Time
) as s
PIVOT
(
SUM(Amount)
For [Month] In (jan, feb, mar, apr, 
may, jun, jul, aug, sep, oct, nov, dec)
)AS pvt

This returns the following results:

CustomerID  jan feb mar apr may jun jul aug sep oct nov dec
3482    NULL    NULL    NULL    NULL    120 NULL    195 NULL    NULL    NULL    NULL    NULL
30249   NULL    NULL    NULL    NULL    NULL    NULL    NULL    70  NULL    35  NULL    NULL
1073743410  NULL    NULL    NULL    60  NULL    NULL    530.5   NULL    653.95  NULL    NULL    20
1073743628  NULL    NULL    NULL    NULL    NULL    50  57.46   NULL    NULL    NULL    35  NULL
1073743632  NULL    NULL    NULL    NULL    NULL    NULL    50  NULL    NULL    NULL    NULL    NULL

In this example the columns "jan", "feb" and "mar" have no results (all values are NULL) and so they shouldn't show. However when I run the query I'm not sure which columns will or will not be empty. Sometimes "oct" may have no results whilst jan will have results.

Can anyone inform me how I would get the results to dynamically only show the columns that have values?

Thank you

Revokez
  • 283
  • 1
  • 3
  • 13
  • 1
    You need dynamic SQL for such a task. A static SQL `SELECT` statement returns always a predefined set of columns. – Giorgos Betsos Sep 10 '16 at 15:06
  • 1
    @GiorgosBetsos - I don't understand why we need dynamic sql here. We need dynamic sql when number of months in an year is unknown ;) – Pரதீப் Sep 10 '16 at 15:47
  • @Prdp I think the OP wants to omit *a whole column* in case all of its values are `NULL`. For example he states months `Jan`, `Feb` and `Mar`. – Giorgos Betsos Sep 10 '16 at 15:49
  • @GiorgosBetsos - Yep, didnt read it well – Pரதீப் Sep 10 '16 at 15:50
  • @GiorgosBetsos Thanks for your answer, I feared this might be the case. However I'm not sure where to begin with updating the above query so that it works as dynamic SQL. Are you able to help with this? – Revokez Sep 10 '16 at 16:08
  • Save the result of `SELECT CustomerID,LEFT(Time,4) Month,SUM(Amount) Amount FROM Payment Group by CustomerID,Time HAVING SUM(Amount) IS NOT NULL` into temp table and build dynamic pivot query against the temp table, see http://stackoverflow.com/questions/12643117/dynamically-create-columns-sql . – Serg Sep 10 '16 at 17:08

0 Answers0