Take column data from SQL table and pivot to row data in spreadsheet

Question

1st of all, I do think the tag is incorrect for this post. I will gladly repost if pointed to a different forum.

Needing to take each row in a table and output as rows in spreadsheet.

Output to look like this.

CREATE TABLE Matrix (
Item nvarchar(10)
, Month01 INT
, Month02 INT
, Month03 INT
);

GO

INSERT INTO Matrix (Item, Month01, Month02, Month03)
VALUES ('First', 10, 11, 12);

INSERT INTO Matrix (Item, Month01, Month02, Month03)
VALUES ('Second', 20, 21, 22);

INSERT INTO Matrix (Item, Month01, Month02, Month03)
VALUES ('Third', 30, 31, 32);

INSERT INTO Matrix (Item, Month01, Month02, Month03)
VALUES ('Fourth', 40, 41, 42);

GO

SELECT * FROM Matrix;

GO

Answer 1

Hi @Lance James,

Please try the following solution. It is using a table row constructor method.

For the reference: https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver16

-- DDL and sample data population, start
DECLARE @tbl TABLE (Item NVARCHAR(10), Month01 INT, Month02 INT, Month03 INT);
INSERT INTO @tbl (Item, Month01, Month02, Month03) VALUES 
('First', 10, 11, 12),
('Second', 20, 21, 22),
('Third', 30, 31, 32),
('Fourth', 40, 41, 42);
-- DDL and sample data population, end

SELECT t.* 
FROM @tbl
CROSS APPLY (VALUES 
		(item, month01),
		(item, month02),
		(item, month03)
		) AS t(a, b);