열 이름으로 주 번호

Question

먼저 내 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;

다음과 같은 출력을 생성합니다. 현재 출력 결과에 만족하지만 이제 각 하위 항목의 주 번호를 열 이름으로 선택하는 것이 좋습니다.

출력은 다음과 같아야합니다. 필요한 출력

Answer 1

기존 쿼리에서 CASE문 을 사용 하거나PIVOT

아래 예는 CASE문을 사용합니다 .

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

그리고 주 번호를 열 이름으로 사용하려면 Dynamic SQL. sp_executesql 확인