SUM CASE WHEN CLAUSE

I have the below Query and I want the Ageing profile to show as a new column
my ageing profile is 0-30, 31-60, 61-90, 91-120, 121-180, 181-365, 365+

I basically want the results to appear lie this with the Net due appearing in the appropriate ageing bracket.

BU   0-30   31-60   61-90   91-120   121-180   181-365   365+
--------------------------------------------------------------
A    
B
C

Query:

select
 BU,
 Ageing,
 sum(NetDue) as Netdue
 from [dbo].[vw_FACT_CONSOL_CREDITORS]
 where date = @date
 Group by BU, Ageing
Order by BU ;

I am new to SQL and just trying to learn so apologise as this is basic stuff
Thanks

Assuming that the Ageing values match the names of the columns in your expected output, then this should work:

SELECT  *
FROM    (
    SELECT  BU, Ageing, NetDue
    FROM    [dbo].[vw_FACT_CONSOL_CREDITORS]
    WHERE   Date = @date
) src
PIVOT
(
    SUM(NetDue)
    FOR Ageing IN ([0-30], [31-60], [61-90], [91-120], [121-180], [181-365], [365+])
) piv

If they are not, then you will need to add some logic to the interior select statement to convert Ageing from whatever it is into those string values (e.g. '0-30', '365+').

As an example to check this, I did the following:

CREATE TABLE x (
    BU INT,
    NetDue SMALLMONEY,
    Ageing VARCHAR(10),
    Date DATE
)

INSERT x
VALUES 
  (1, 100, '31-60', '2017-04-01'),
  (2, 25.47, '31-60', '2017-04-01'),
  (1, 90.17, '31-60', '2017-05-01'),
  (1, 16.42, '181-365', '2017-04-01'),
  (2, 99.99, '365+', '2017-04-01')

SELECT  *
FROM    (
    SELECT  BU, Ageing, NetDue
    FROM    x
    WHERE   Date = '2017-04-01'
) src
PIVOT
(
    SUM(NetDue)
    FOR Ageing IN ([0-30], [31-60], [61-90], [91-120], [121-180], [181-365], [365+])
) piv

which produced the output:

BU  0-30    31-60   61-90   91-120  121-180 181-365 365+
1   NULL    100.00  NULL    NULL    NULL    16.42   NULL
2   NULL    25.47   NULL    NULL    NULL    NULL    99.99

All the information (and more) for determining this logic can be found here.

StackExchange Database Administrators Q#188574, answer score: 3