SQL row column data swapping

Question

Currently I'm building a tool to add/update translation data and generate an XML file for the program that is using that data.

I read in the XMLfile and store it in a database so that we can also use the data later to create other translation files.

To get the data from the database into the tool I generate a SQL query based on the amound of possible translations (stored in a different table) an join them with the translation data based on an ID. Inside the tool I'm using a dataTable and for this I'm trying to dynamicly swap rowdata to an column. This should be easy but I'm struggeling with it.

There are two tables, one with the locales and one with the data.

[dbo].[Locales]
    [Id] [int] IDENTITY(1,1) NOT NULL
    [Locale] [nvarchar](10) NOT NULL

[dbo].[Translations]
    [Id] [int] IDENTITY(1,1) NOT NULL
    [TranslationID] [int] NOT NULL
    [Text] [nvarchar](max) NULL
    [LocaleID] [int] NOT NULL

The translation data is look like:

TranslationID	Text	Locale
1	Conexiones	es_ES
1	Anschlüsse	de_DE
1	Connection points	en_US
1	Aansluitingen	nl_NL
2	Spannungsversorgung	de_DE
2	Power supply	en_US

How I want it:

TranslationID	nl_NL	en_US	de_DE	es_ES	x_X
1	Aansluitingen	Connection points	Anschlüsse	Conexiones	null
2	Voeding	Power suply	Spannungsversorgung	null	some language

I have tried to get the data with use of LEFT JOIN and OUTER JOIN without succes. The problem is that there is not for every translation data availible (thats the reason for building a tool for it). I also have found "PIVOT" but it looks like it can not generate the data as I want it because PIVOT wants an aggregation function.

Thanks in advance!

Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) In this case it is simplified by already having a list of locales, which means you don't need a temp table — Charlieface, Jul 01 '21 at 13:11
You say 'PIVOT wants an aggregation function'. If you never have more than one 'locale' per 'translationID', then you're fine. Just do a `min` or `max` and it will grab the single value. The real struggle is needing it to be dynamic. The link from Charlieface should help you. — pwilcox, Jul 01 '21 at 13:13
[ The problem is that there is not for every translation data availible (thats the reason for building a tool for it)] you can use COALESE(col1, col2, col3, '') to replace value if it null — Power Mouse, Jul 01 '21 at 14:17
"Just do a min or max and it will grab the single value." -- I've done this with PIVOT, but I have to agree with j wijnands that it seems awkward and annoying to aggregate when aggregation is not really what is desired. — Neal Burns, Jul 02 '21 at 18:48

score 0 · Accepted Answer · answered Jul 02 '21 at 19:30

If you don't want to use PIVOT, you can get the list of unique translationIDs:

SELECT TranslationID
FROM Translations
GROUP BY TranslationID

and then grab the individual locales with subqueries (I have put a Locale column on Translations for the sake of concision):

SELECT
    UniqueTID,
    (SELECT Text FROM Translations t WHERE (TranslationID = UniqueTID AND Locale = 'nl_NL') as nl_NL,
    (SELECT Text FROM Translations t WHERE (TranslationID = UniqueTID AND Locale = 'en_US') as en_US,
    (SELECT Text FROM Translations t WHERE (TranslationID = UniqueTID AND Locale = 'de_DE') as de_DE
FROM (
    SELECT TranslationID as UniqueTID
    FROM Translations
    GROUP BY TranslationID
) uniqTIDs

or with outer joins:

SELECT
    UniqueTID,
    nlTrans.Text as nl_NL,
    enTrans.Text as en_US,
    deTrans.Text as de_DE
FROM (
    SELECT TranslationID as UniqueTID
    FROM Translations
    GROUP BY TranslationID
) uniqueTIDs
    LEFT JOIN Translations nlTrans ON (nlTrans.TranslationID = UniqueTID AND nlTrans.Locale = 'nl_NL')
    LEFT JOIN Translations enTrans ON (enTrans.TranslationID = UniqueTID AND enTrans.Locale = 'en_US')
    LEFT JOIN Translations deTrans ON (deTrans.TranslationID = UniqueTID AND deTrans.Locale = 'de_DE')

Thanks for the response. I have used this as a base for my new query which now works as preferred. — j wijnands, Jul 05 '21 at 07:35

SQL row column data swapping

1 Answers1