week number as column name

first of all my sql statement:

SELECT DISTINCT
(
    SELECT SUM(Preis1)
    FROM tblBuchungsdaten
    WHERE (Datum BETWEEN DATEADD(wk, -7, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) AND DATEADD(wk, -6, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))
)AS '7 weeks ago',
(
    SELECT SUM(Preis1)
    FROM tblBuchungsdaten
    WHERE (Datum BETWEEN DATEADD(wk, -6, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) AND DATEADD(wk, -5, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))
)AS '6 weeks ago',
(
    SELECT SUM(Preis1)
    FROM tblBuchungsdaten
    WHERE (Datum BETWEEN DATEADD(wk, -5, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) AND DATEADD(wk, -4, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))
)AS '5 weeks ago',
(
    SELECT SUM(Preis1)
    FROM tblBuchungsdaten
    WHERE (Datum BETWEEN DATEADD(wk, -4, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) AND DATEADD(wk, -3, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))
)AS '4 weeks ago',
(
    SELECT SUM(Preis1)
    FROM tblBuchungsdaten
    WHERE (Datum BETWEEN DATEADD(wk, -3, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) AND DATEADD(wk, -2, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))
)AS '3 weeks ago',
(
    SELECT SUM(Preis1)
    FROM tblBuchungsdaten
    WHERE (Datum BETWEEN DATEADD(wk, -2, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) AND DATEADD(wk, -1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))
)AS '2 weeks ago',
(
    SELECT SUM(Preis1)
    FROM tblBuchungsdaten
    WHERE (Datum BETWEEN DATEADD(wk, -1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) AND DATEADD(wk, 0, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))
)AS '1 week ago'
FROM tblBuchungsdaten;

It creates this output: current output That far I´m happy with the results, but I would love now having the week number of each sub select as column name.

The output should look like this: needed output

Answer

On your existing query, you can change to use CASE statement or PIVOT

example below uses the CASE statement

SELECT  SUM (CASE WHEN Datum BETWEEN DATEADD(wk, -7, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) 
                             AND     DATEADD(wk, -6, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) 
          THEN Preis1
          END) as '7 weeks ago',
        SUM (CASE WHEN Datum BETWEEN DATEADD(wk, -6, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) 
                             AND     DATEADD(wk, -5, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) 
          THEN Preis1
          END) as '6 weeks ago',
        . . . . .
FROM    tblBuchungsdaten

And to have the week number as column name, you need to use Dynamic SQL. Check out sp_executesql