número da semana como nome da coluna

Question

antes de mais nada, minha instrução sql:

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;

Ele cria esta saída: saída atual Até agora estou feliz com os resultados, mas eu adoraria agora ter o número da semana de cada sub-seleção como nome da coluna.

A saída deve ser semelhante a esta: saída necessária

Answer 1

Em sua consulta existente, você pode alterar para usar CASEdeclaração ouPIVOT

o exemplo abaixo usa a CASEdeclaração

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

E para ter o número da semana como nome da coluna, você precisa usar Dynamic SQL. Confira sp_executesql