sql query PIVOT

Question

I would like to arrange the sum of OrderValue by the period. My SQL query now displays in a tabular format and I want it to be in one line. If the OrderValue due is in this current, it should be under column 0, and if due next month, then it must me under column 1 and so on. Please see my SQL query

ALTER PROCEDURE [dbo].[sp_GetInvoicedPayments]
@CustomerID int   
AS
BEGIN   
DECLARE @endOfCurrentMonth DATE = EOMONTH(GETDATE())

SELECT [data].CustomerID, [data].[Period], SUM([data].OrderValue) AS 
OrderValue 
FROM (
SELECT pms.CustomerID, pms.OrderValue,
        CASE
            WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= paymentInfo.CurrentDueMonth) THEN 0
            WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, DATEADD(MONTH, 1, paymentInfo.CurrentDueMonth)) + 1,- 1)) ) THEN 1
            WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, DATEADD(MONTH, 2, paymentInfo.CurrentDueMonth)) + 1,- 1))) THEN  2
            WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, DATEADD(MONTH, 3, paymentInfo.CurrentDueMonth)) + 1,- 1))) THEN  3
            WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, DATEADD(MONTH, 4, paymentInfo.CurrentDueMonth)) + 1,- 1))) THEN  4
        END AS [Period]
FROM PaymentMilestoneSummary pms
INNER JOIN (
    SELECT cus.ID AS CustomerID,
    CASE 
        WHEN cus.PaymentStatusID = 1 THEN @endOfCurrentMonth
        WHEN cus.PaymentStatusID = 2 THEN (SELECT CAST(DATEADD(month, - 1, @endOfCurrentMonth) AS DATE))
        WHEN cus.PaymentStatusID = 3 THEN (SELECT CAST(DATEADD(month, - 2, @endOfCurrentMonth) AS DATE))
        WHEN cus.PaymentStatusID = 4 THEN (SELECT CAST(DATEADD(month, - 3, @endOfCurrentMonth) AS DATE))
        WHEN cus.PaymentStatusID = 5 THEN (SELECT CAST(DATEADD(month, - 4, @endOfCurrentMonth) AS DATE))
    END AS CurrentDueMonth
    FROM Company cus 
) paymentInfo ON pms.CustomerID = paymentInfo.CustomerID AND paymentInfo.CustomerID= @CustomerID
)[data]
 GROUP BY [data].CustomerID, [data].[Period]
END

This is what I get:

This is an example of how I would like it to be:

Answer 1

You can do it like following using PIVOT and CTE

;with cte as
(
 --wrap you existing query
)

SELECT   
[0], [1], [2], [3], [4]  
FROM  
(select Period, OrderValue from cte) AS SourceTable  
PIVOT  
(  
    max(OrderValue)  
    FOR Period IN ([0], [1], [2], [3], [4])  
) AS PivotTable;  

Your procedure should look like this.

ALTER PROCEDURE [dbo].[sp_GetInvoicedPayments]
@CustomerID int   
AS
BEGIN   
DECLARE @endOfCurrentMonth DATE = EOMONTH(GETDATE())
;with CTE AS
(
    SELECT [data].CustomerID, [data].[Period], SUM([data].OrderValue) AS 
    OrderValue 
    FROM (
    SELECT pms.CustomerID, pms.OrderValue,
            CASE
                WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= paymentInfo.CurrentDueMonth) THEN 0
                WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, DATEADD(MONTH, 1, paymentInfo.CurrentDueMonth)) + 1,- 1)) ) THEN 1
                WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, DATEADD(MONTH, 2, paymentInfo.CurrentDueMonth)) + 1,- 1))) THEN  2
                WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, DATEADD(MONTH, 3, paymentInfo.CurrentDueMonth)) + 1,- 1))) THEN  3
                WHEN ((SELECT CAST(pms.ExpectedDate AS DATE)) <= DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, DATEADD(MONTH, 4, paymentInfo.CurrentDueMonth)) + 1,- 1))) THEN  4
            END AS [Period]
    FROM PaymentMilestoneSummary pms
    INNER JOIN (
        SELECT cus.ID AS CustomerID,
        CASE 
            WHEN cus.PaymentStatusID = 1 THEN @endOfCurrentMonth
            WHEN cus.PaymentStatusID = 2 THEN (SELECT CAST(DATEADD(month, - 1, @endOfCurrentMonth) AS DATE))
            WHEN cus.PaymentStatusID = 3 THEN (SELECT CAST(DATEADD(month, - 2, @endOfCurrentMonth) AS DATE))
            WHEN cus.PaymentStatusID = 4 THEN (SELECT CAST(DATEADD(month, - 3, @endOfCurrentMonth) AS DATE))
            WHEN cus.PaymentStatusID = 5 THEN (SELECT CAST(DATEADD(month, - 4, @endOfCurrentMonth) AS DATE))
        END AS CurrentDueMonth
        FROM Company cus 
    ) paymentInfo ON pms.CustomerID = paymentInfo.CustomerID AND paymentInfo.CustomerID= @CustomerID
    )[data]
     GROUP BY [data].CustomerID, [data].[Period]
)

SELECT [0], [1], [2], [3], [4]  
FROM  
(select Period, OrderValue from cte) AS SourceTable  
PIVOT  
(  
    max(OrderValue)  
    FOR Period IN ([0], [1], [2], [3], [4])  
) AS PivotTable; 

END

Edit:

how would I then sum up those values I get from that one line?

To sum, you can change the pivot query like following.

SELECT   
[0], [1], [2], [3], [4] , s as [Sum]
FROM  
(select Period, OrderValue, sum(OrderValue) over() s from cte) AS SourceTable  
PIVOT  
(  
    max(OrderValue)  
    FOR Period IN ([0], [1], [2], [3], [4])  
) AS PivotTable;