OrderId OrderDate Amount
----------- ---------- ------
1 2007-01-01 10.50
2 2007-01-26 12.50
3 2007-01-30 12.00
4 2007-02-14 13.75
5 2007-02-20 10.00
6 2007-03-06 15.00
7 2007-03-10 17.50
8 2007-03-29 20.00
OrderYear Jan Feb Mar
----------- ----- ----- -----
2007 35.00 23.75 52.50
CREATE TABLE Orders (
order_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
amount DECIMAL(8, 2) NOT NULL DEFAULT 0
CHECK (amount >= 0));
INSERT INTO Orders
(order_date, amount)
SELECT '20070101', 10.50
UNION ALL
SELECT '20070126', 12.50
UNION ALL
SELECT '20070130', 12.00
UNION ALL
SELECT '20070214', 13.75
UNION ALL
SELECT '20070220', 10.00
UNION ALL
SELECT '20070306', 15.00
UNION ALL
SELECT '20070310', 17.50
UNION ALL
SELECT '20070329', 20.00;
SELECT DATEPART(yyyy, order_date) AS OrderYear,
SUM(CASE WHEN DATEPART(m, order_date) = 1
THEN amount ELSE 0 END) AS 'Jan',
SUM(CASE WHEN DATEPART(m, order_date) = 2
THEN amount ELSE 0 END) AS 'Feb',
SUM(CASE WHEN DATEPART(m, order_date) = 3
THEN amount ELSE 0 END) AS 'Mar'
FROM Orders
GROUP BY DATEPART(yyyy, order_date);
CREATE TABLE MonthMatrix (
month_nbr INT NOT NULL PRIMARY KEY
CHECK (month_nbr BETWEEN 1 AND 12),
jan INT NOT NULL DEFAULT 0
CHECK (jan IN (0, 1)),
feb INT NOT NULL DEFAULT 0
CHECK (feb IN (0, 1)),
mar INT NOT NULL DEFAULT 0
CHECK (mar IN (0, 1)),
apr INT NOT NULL DEFAULT 0
CHECK (apr IN (0, 1)),
may INT NOT NULL DEFAULT 0
CHECK (may IN (0, 1)),
jun INT NOT NULL DEFAULT 0
CHECK (jun IN (0, 1)),
jul INT NOT NULL DEFAULT 0
CHECK (jul IN (0, 1)),
aug INT NOT NULL DEFAULT 0
CHECK (aug IN (0, 1)),
sep INT NOT NULL DEFAULT 0
CHECK (sep IN (0, 1)),
oct INT NOT NULL DEFAULT 0
CHECK (oct IN (0, 1)),
nov INT NOT NULL DEFAULT 0
CHECK (nov IN (0, 1)),
dec INT NOT NULL DEFAULT 0
CHECK (dec IN (0, 1)));
-- Populate the matrix table
INSERT INTO MonthMatrix (month_nbr, jan) VALUES (1, 1);
INSERT INTO MonthMatrix (month_nbr, feb) VALUES (2, 1);
INSERT INTO MonthMatrix (month_nbr, mar) VALUES (3, 1);
INSERT INTO MonthMatrix (month_nbr, apr) VALUES (4, 1);
INSERT INTO MonthMatrix (month_nbr, may) VALUES (5, 1);
INSERT INTO MonthMatrix (month_nbr, jun) VALUES (6, 1);
INSERT INTO MonthMatrix (month_nbr, jul) VALUES (7, 1);
INSERT INTO MonthMatrix (month_nbr, aug) VALUES (8, 1);
INSERT INTO MonthMatrix (month_nbr, sep) VALUES (9, 1);
INSERT INTO MonthMatrix (month_nbr, oct) VALUES (10, 1);
INSERT INTO MonthMatrix (month_nbr, nov) VALUES (11, 1);
INSERT INTO MonthMatrix (month_nbr, dec) VALUES (12, 1);
-- Use the matrix table to pivot
SELECT DATEPART(yyyy, order_date) AS OrderYear,
SUM(amount * jan) AS 'Jan',
SUM(amount * feb) AS 'Feb',
SUM(amount * mar) AS 'Mar'
FROM Orders AS O
JOIN MonthMatrix AS M
ON DATEPART(m, O.order_date) = M.month_nbr
GROUP BY DATEPART(yyyy, order_date);
SELECT DATEPART(yyyy, order_date) AS OrderYear,
SUM(amount * (1 - ABS(SIGN(DATEPART(m, order_date) - 1)))) AS 'Jan',
SUM(amount * (1 - ABS(SIGN(DATEPART(m, order_date) - 2)))) AS 'Feb',
SUM(amount * (1 - ABS(SIGN(DATEPART(m, order_date) - 3)))) AS 'Mar'
FROM Orders
GROUP BY DATEPART(yyyy, order_date);
SELECT OrderYear,
[1] AS 'Jan',
[2] AS 'Feb',
[3] AS 'Mar'
FROM (SELECT DATEPART(yyyy, order_date),
DATEPART(m, order_date),
amount
FROM Orders) AS O (OrderYear, month_nbr, amount)
PIVOT
(SUM(amount) FOR month_nbr IN ([1], [2], [3])) AS P;
Labels: cross-tab, pivot, sql server, t-sql programming