By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @TRS_TAB TABLE(ITEM VARCHAR(10), [DATE] DATE, QTY INT, AMOUNT DECIMAL(10,2))
INSERT @TRS_TAB
VALUES
('T1', '2022-01-01', 1, 1.50),
('T2', '2022-01-02', 2, 6.00),
('T1', '2022-01-01', 2, 3.00),
('T3', '2022-01-04', 1, 2.50),
('T2', '2022-01-05', 3, 9.00),
('T1', '2022-01-02', 2, 3.00),
('T3', '2022-01-03', 2, 5.00)
SELECT TRS.ITEM,
SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-01' THEN TRS.QTY END) D1Q,
SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-02' THEN TRS.QTY END) D2Q,
SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-03' THEN TRS.QTY END) D3Q,
SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-04' THEN TRS.QTY END) D4Q,
SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-05' THEN TRS.QTY END) D5Q,
SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-06' THEN TRS.QTY END) D6Q,
SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-07' THEN TRS.QTY END) D7Q,
SUM(TRS.QTY) AS TOTQ,
SUM(TRS.AMOUNT) AS TOTA
FROM @TRS_TAB TRS
WHERE TRS.DATE BETWEEN '2022-01-01' AND '2022-01-07'
GROUP BY TRS.ITEM
ORDER BY TRS.ITEM
| ITEM | D1Q | D2Q | D3Q | D4Q | D5Q | D6Q | D7Q | TOTQ | TOTA |
|---|---|---|---|---|---|---|---|---|---|
| T1 | 3 | 2 | null | null | null | null | null | 5 | 7.50 |
| T2 | null | 2 | null | null | 3 | null | null | 5 | 15.00 |
| T3 | null | null | 2 | 1 | null | null | null | 3 | 7.50 |
Warning: Null value is eliminated by an aggregate or other SET operation.