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

Lance James 366 Reputation points
2023-07-14T19:10:03.7833333+00:00

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.

User's image

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,240 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,561 Reputation points
    2023-07-14T19:25:15.77+00:00

    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);
    
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful