OracleDB行转列技术问询：按指定列值将行数据映射至对应列

Got it, let's work through this row-to-column pivot challenge you're facing in Oracle SQL Developer. This is a common scenario when you need to restructure your data to show language-specific texts side-by-side in the same row.

First, let's assume your source table has a grouping key (like an item ID) to tie the different language entries together—without this, we can't logically merge the DE and IT rows into one. Here's a sample table structure to match your requirements:

-- Sample source table (adjust column names/types to match your actual schema)
CREATE TABLE product_texts (
  item_id NUMBER,          -- Grouping key to link same-item texts
  spr_id VARCHAR2(2),      -- Language code (DE/IT)
  arttxtnummer NUMBER,     -- Text number (1 in your case)
  arttxttxt VARCHAR2(200)  -- The actual text content
);

And some sample data to test with:

INSERT INTO product_texts VALUES (100, 'DE', 1, 'Dies ist eine deutsche Beschreibung');
INSERT INTO product_texts VALUES (100, 'IT', 1, 'Questa è una descrizione italiana');
INSERT INTO product_texts VALUES (200, 'DE', 1, 'Ein weiteres Produkt auf Deutsch');

The PIVOT Solution #

Oracle's built-in PIVOT clause is perfect for this transformation. Here's the query that will do exactly what you need:

SELECT
  item_id,
  "TXT1-DE",
  "TXT1-IT"
FROM product_texts
PIVOT (
  -- We use MAX() here because each (item_id, spr_id, arttxtnummer) has one unique value
  -- Any aggregate function (MIN/AVG) would work here since there's only one record per group
  MAX(arttxttxt)
  -- Define the columns we're pivoting on, and map each combination to our target column names
  FOR (spr_id, arttxtnummer) IN (
    ('DE', 1) AS "TXT1-DE",
    ('IT', 1) AS "TXT1-IT"
  )
);

What This Does: #

• Grouping: The query groups records by item_id, so all texts for the same item end up in one row.
• Pivoting: The PIVOT clause takes the combination of spr_id (language) and arttxtnummer (text number), then maps each unique combination to a new column. For example, when spr_id = 'DE' and arttxtnummer = 1, the arttxttxt value goes into TXT1-DE.
• Quoted Column Names: Notice we use double quotes around TXT1-DE and TXT1-IT—Oracle requires this for identifiers with special characters like hyphens.

Extending the Query #

If you need to add more language/text number combinations later (like TXT2-DE or TXT1-FR), just add new entries to the IN clause:

FOR (spr_id, arttxtnummer) IN (
  ('DE', 1) AS "TXT1-DE",
  ('IT', 1) AS "TXT1-IT",
  ('DE', 2) AS "TXT2-DE",
  ('FR', 1) AS "TXT1-FR"
)

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