-- Table with orders
CREATE TABLE Orders (
order_id INT NOT NULL PRIMARY KEY,
order_date DATETIME NOT NULL
DEFAULT CURRENT_TIMESTAMP,
amount DECIMAL(8, 2) NOT NULL DEFAULT 0
CHECK (amount >= 0));
-- Sample data
INSERT INTO Orders
(order_id, order_date, amount)
SELECT 1, '20070101', 10.50
UNION ALL
SELECT 2, '20070126', 12.50
UNION ALL
SELECT 3, '20070130', 12.00
UNION ALL
SELECT 4, '20070214', 13.75
UNION ALL
SELECT 5, '20070220', 10.00
UNION ALL
SELECT 6, '20070306', 15.00
UNION ALL
SELECT 7, '20070310', 17.50
UNION ALL
SELECT 8, '20070329', 20.00;
-- Build list of column values to pivot
DECLARE @cols NVARCHAR(1000);
SELECT @cols =
STUFF((SELECT N'],[' + year_month
FROM (SELECT DISTINCT CONVERT(NCHAR(7), order_date, 126)
FROM Orders) AS O(year_month)
ORDER BY year_month
FOR XML PATH('')
), 1, 2, '') + N']';
-- Build dynamic SQL query for pivoting
DECLARE @sql NVARCHAR(2000);
SET @sql =
N'SELECT order_year, ' + @cols +
N'FROM (SELECT DATEPART(yyyy, order_date) AS order_year, ' +
N'CONVERT(NCHAR(7), order_date, 126) AS year_month, ' +
N'amount ' +
N'FROM Orders) AS O ' +
N'PIVOT ' +
N'(SUM(amount) FOR year_month IN (' + @cols + N')) AS P;';
EXEC(@sql);
/*
Results:
order_year 2007-01 2007-02 2007-03
----------- -------- -------- --------
2007 35.00 23.75 52.50
*/
Labels: cross-tab, pivot, t-sql programming