How to show rows of a table as columns in SQL

Question

I have two tables LANGUAGE:

CREATE TABLE LANGUAGE
(
    LANGUAGE_ID nvarchar(10)
);

INSERT INTO LANGUAGE (LANGUAGE_ID)
VALUES ('de-DE'), ('en-EN'), ('de-US'), ('fr-FR'), ('es-ES');

and a table TRANSLATION which contains all translations of a TEXT_ID in several languages:

CREATE TABLE TRANSLATION
(
    TEXT_ID nvarchar(20), 
    LANGUAGE_ID varchar(10), 
    TRANSLATION varchar(200)
);

INSERT INTO TRANSLATION (TEXT_ID, LANGUAGE_ID, TRANSLATION)
VALUES
    ('wire', 'de-DE', 'Draht'),
    ('wire', 'en-EN', 'Wire'),
    ('wire', 'en-US', 'Wire'),
    ('wire', 'fr-FR', 'fr:Draht'),
    ('wire', 'es-ES', 'es:Draht'),
    ('brush', 'de-DE', 'Buerste'),
    ('brush', 'en-EN', 'Brush'),
    ('brush', 'en-US', 'us_Brush'),
    ('screw', 'en-US', 'Screw');

As a result I want to get a list of all translations of a TEXT_ID in a single row:

TEXT_ID     de-DE       en-EN       en-US       fr-FR       es-ES
--------------------------------------------------------------------
wire        Draht       Wire        Wire        fr:Draht    es:Draht
brush       Buerste     Brush       us_Brush
screw                               Screw

Is there a pure SQL way to do that?

Currently I am using SQL Server. But potentially we want to migrate to another database.

Example SQLFiddle

if number of languages is unknown, then you can start to google "dynamic pivot".If it is defined, then pivot — Sergey
– Sergey, Commented Jul 22, 2022 at 9:12

Tim Biegeleisen · Accepted Answer · 2022-07-22 09:17:43Z

We can try a pivot query with the help of some calendar table logic. First, build an intermediate table of all keywords and languages. Then, join this to the TRANSLATION table and pivot.

SELECT
    t.TEXT_ID,
    MAX(CASE WHEN l.LANGUAGE_ID = 'de-DE' THEN tr.TRANSLATION END) AS [de-DE],
    MAX(CASE WHEN l.LANGUAGE_ID = 'en-EN' THEN tr.TRANSLATION END) AS [en-EN],
    MAX(CASE WHEN l.LANGUAGE_ID = 'en-US' THEN tr.TRANSLATION END) AS [en-US],
    MAX(CASE WHEN l.LANGUAGE_ID = 'fr-FR' THEN tr.TRANSLATION END) AS [fr-FR],
    MAX(CASE WHEN l.LANGUAGE_ID = 'es-ES' THEN tr.TRANSLATION END) AS [es-ES]
FROM (SELECT DISTINCT TEXT_ID FROM TRANSLATION) t
CROSS JOIN LANGUAGE l
LEFT JOIN TRANSLATION tr
    ON tr.TEXT_ID = t.TEXT_ID AND
       tr.LANGUAGE_ID = l.LANGUAGE_ID
GROUP BY t.TEXT_ID;

Note that if you need a dynamic number of columns, then you might have to resort to dynamic SQL, which basically means writing SQL Server code to generate a query.

Collectives™ on Stack Overflow

How to show rows of a table as columns in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related