将多列转换为对应行：SQL列转行需求及实现尝试

Got it, let's work through this column-to-row conversion problem. First, a quick note on your current query: using two separate CROSS APPLY blocks creates an unintended Cartesian product between your AttributeID and AttributeData values (you’d get 9 rows per product instead of the expected 3). We can fix that, plus explore the more efficient UNPIVOT method you mentioned—perfect for handling your 15 column pairs.

Corrected CROSS APPLY Approach #

This version keeps each AttributeID_n paired with its matching AttributeData_n in a single CROSS APPLY block, ensuring the correct 1:1 mapping:

DROP TABLE IF EXISTS Attribute;
CREATE TABLE Attribute (
 Producttitle varchar(200),
 AttributeID_1 varchar(50),
 AttributeData_1 varchar(50),
 AttributeID_2 varchar(50),
 AttributeData_2 varchar(50),
 AttributeID_3 varchar(50),
 AttributeData_3 varchar(50)
 );
INSERT INTO Attribute VALUES ('title1', '3145', 'Specific', '30', 'Yes', '40', 'Pink')
INSERT INTO Attribute VALUES ('title2', '17', 'Stainless', '19', 'smoke', '19', 'Something');

-- Corrected query to pair AttributeID and AttributeData correctly
SELECT 
  Producttitle,
  AttributeID,
  AttributeData
FROM Attribute
CROSS APPLY (
  SELECT AttributeID_1, AttributeData_1 UNION ALL
  SELECT AttributeID_2, AttributeData_2 UNION ALL
  SELECT AttributeID_3, AttributeData_3
) AS UnpivotedData(AttributeID, AttributeData);

To scale this to 15 column pairs, just extend the UNION ALL list to include all AttributeID_n/AttributeData_n pairs.

UNPIVOT Approach (Better Performance for Large Column Sets) #

UNPIVOT is purpose-built for this kind of transformation, and it’s often more efficient than chained UNION ALL statements—especially with 15 column pairs. We’ll unpivot the ID and Data columns separately, then join them using the numeric suffix in column names to keep pairs matched:

SELECT
  up.Producttitle,
  up.AttributeID,
  upd.AttributeData
FROM (
  -- Unpivot all AttributeID columns
  SELECT 
    Producttitle,
    AttributeID,
    -- Extract the numeric suffix to match with AttributeData rows
    CAST(RIGHT(AttributeColumn, LEN(AttributeColumn) - CHARINDEX('_', AttributeColumn)) AS INT) AS AttributeNumber
  FROM Attribute
  UNPIVOT (
    AttributeID FOR AttributeColumn IN (AttributeID_1, AttributeID_2, AttributeID_3)
  ) AS unpivotID
) AS up
JOIN (
  -- Unpivot all AttributeData columns
  SELECT 
    Producttitle,
    AttributeData,
    CAST(RIGHT(AttributeColumn, LEN(AttributeColumn) - CHARINDEX('_', AttributeColumn)) AS INT) AS AttributeNumber
  FROM Attribute
  UNPIVOT (
    AttributeData FOR AttributeColumn IN (AttributeData_1, AttributeData_2, AttributeData_3)
  ) AS unpivotData
) AS upd
  ON up.Producttitle = upd.Producttitle
  AND up.AttributeNumber = upd.AttributeNumber;

For 15 columns, simply add all AttributeID_n values to the first UNPIVOT’s IN clause, and all AttributeData_n values to the second one.

Quick Comparison #

• CROSS APPLY: Simple to write for small column sets, but can get verbose with 15 pairs.
• UNPIVOT: Cleaner syntax for large column counts and generally better performance, as it leverages SQL Server’s built-in unpivoting logic.

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