请求提供SQL结果集行列转置的可用T-SQL实现代码

Hey there! I’ve been in your shoes before—scouring the web for SQL pivot solutions that either don’t fit your exact data structure or leave out critical steps that make them fail when you run them. Since you didn’t share your raw query results and expected pivoted output, I’ll cover the two most common T-SQL pivoting scenarios with fully runnable code that you can adapt to your data.

1. Fixed Column Pivoting (For Known, Static Columns) #

If you already know the exact columns you want to pivot your rows into, use T-SQL’s built-in PIVOT operator. This example uses a typical sales dataset, but you can swap out the table/column names to match your data.

-- Create a temporary table to hold sample data (replace with your actual query)
CREATE TABLE #SalesData (
    Category VARCHAR(50),
    Product VARCHAR(50),
    SalesAmount INT
);

-- Insert sample data (replace this with your actual data source)
INSERT INTO #SalesData (Category, Product, SalesAmount)
VALUES
    ('Electronics', 'Laptop', 1800),
    ('Electronics', 'Smartphone', 900),
    ('Apparel', 'T-Shirt', 35),
    ('Apparel', 'Jeans', 65);

-- Run the pivot query
SELECT 
    Category,
    [Laptop],
    [Smartphone],
    [T-Shirt],
    [Jeans]
FROM (
    -- Subquery to select the base data we need to pivot
    SELECT Category, Product, SalesAmount
    FROM #SalesData
) AS SourceData
PIVOT (
    -- Use SUM/AVG/MAX/MIN based on your data type and needs
    SUM(SalesAmount)
    -- Define which column's values become our new columns
    FOR Product IN ([Laptop], [Smartphone], [T-Shirt], [Jeans])
) AS PivotedResults;

-- Clean up the temporary table
DROP TABLE #SalesData;

Key Notes: #

• If you’re pivoting text values instead of numbers, use MAX(Value) or MIN(Value) instead of SUM (since you can’t aggregate text with sum).
• Replace #SalesData, column names, and the product list with your actual data.

2. Dynamic Column Pivoting (For Unknown/Variable Columns) #

If your pivot columns change regularly (e.g., new products get added, or you don’t want to hardcode column names), use dynamic SQL to automatically generate the pivot column list.

-- Create a temporary table with dynamic sample data
CREATE TABLE #DynamicSalesData (
    Category VARCHAR(50),
    Product VARCHAR(50),
    SalesAmount INT
);

-- Insert sample data (add more products to test the dynamic behavior)
INSERT INTO #DynamicSalesData (Category, Product, SalesAmount)
VALUES
    ('Electronics', 'Laptop', 1800),
    ('Electronics', 'Smartphone', 900),
    ('Electronics', 'Tablet', 450),
    ('Apparel', 'T-Shirt', 35),
    ('Apparel', 'Jeans', 65),
    ('Apparel', 'Jacket', 120);

-- Declare variables to build our dynamic query
DECLARE @PivotColumns NVARCHAR(MAX), @FullQuery NVARCHAR(MAX);

-- Get all distinct product names to use as pivot columns
SET @PivotColumns = STUFF(
    (SELECT DISTINCT ', [' + Product + ']'
     FROM #DynamicSalesData
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 1, ''
);

-- Build the full pivot query dynamically
SET @FullQuery = '
SELECT 
    Category,
    ' + @PivotColumns + '
FROM (
    SELECT Category, Product, SalesAmount
    FROM #DynamicSalesData
) AS SourceData
PIVOT (
    SUM(SalesAmount)
    FOR Product IN (' + @PivotColumns + ')
) AS PivotedResults;';

-- Execute the dynamic query
EXEC sp_executesql @FullQuery;

-- Clean up the temporary table
DROP TABLE #DynamicSalesData;

Key Notes: #

• This code automatically detects all unique values in the Product column and turns them into columns.
• Adjust the aggregation function (SUM, MAX, etc.) to match your data type.

Bonus: Pivoting Key-Value Pairs #

If your raw data is in a key-value format (e.g., user attributes stored as rows), use this adapted pivot code:

CREATE TABLE #KeyValueData (
    UserID INT,
    AttributeName VARCHAR(50),
    AttributeValue VARCHAR(50)
);

INSERT INTO #KeyValueData (UserID, AttributeName, AttributeValue)
VALUES
    (1, 'FirstName', 'Carmine'),
    (1, 'LastName', 'Smith'),
    (1, 'Email', 'carmine@example.com'),
    (2, 'FirstName', 'Jane'),
    (2, 'LastName', 'Doe');

SELECT 
    UserID,
    [FirstName],
    [LastName],
    [Email]
FROM (
    SELECT UserID, AttributeName, AttributeValue
    FROM #KeyValueData
) AS SourceData
PIVOT (
    MAX(AttributeValue) -- Use MAX for text values
    FOR AttributeName IN ([FirstName], [LastName], [Email])
) AS PivotedResults;

DROP TABLE #KeyValueData;

If your actual data structure or expected output doesn’t match these examples, just share the exact schema of your raw query results and what you want the final pivoted table to look like—I’ll adjust the code to fit perfectly!

内容的提问来源于stack exchange，提问作者Carmine