如何将不同的字段值转换为SQLite中的列? [英] How to convert distinct field values to columns in SQLite?

问题描述

我有一个看起来像这样的表:

I have a table that looks like this:

WidgetName | LanguageId | Text
--------------------------------
lbNext     | 1031       | weiter
lbNext     | 1033       | next
btConnect  | 1031       | verbinden
btConnect  | 1033       | connect

随着可以添加新的翻译(和新的语言)，不同LanguageId的数量也有所不同.我想从这张表中得到的是我们可以提供给翻译器的另一张表.它应该具有以下结构:

The number of different LanguageIds is varying as new translations (and new languages) can be added. What I want to get from this table is another table that we can give to our translator. It should have this structure:

WidgetName | 1031      | 1033    | [...other LanguageIds]
----------------------------------------------------------
lbNext     | weiter    | next    | [...other translations]
btConnect  | verbinden | connect | [...other translations]

有没有一种方法可以通过SQL查询获得这样的表?我想问题可能出在不同语言的数量上...

Is there a way to get a table like this with an SQL query? I guess the problem might be the varying number of different languages...

推荐答案

这是您需要的完整查询(我使用Table_1作为表名，它很容易替换)

Here you are the complete query you need (i used Table_1 as the table name, its easy to replace)

您需要以棘手的方式使用PIVOT:

You need to use PIVOT in a tricky way:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(LanguageId) 
                    from Table_1
                    group by LanguageId
                    order by LanguageId
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
select @cols


set @query = N'SELECT widgetName, ' + @cols + N' from 
             (
                select widgetName, Text, LanguageId
                from Table_1
            ) x
            pivot 
            (
                max(Text)
                for LanguageId in (' + @cols + N')
            ) p '

exec sp_executesql @query;

这篇关于如何将不同的字段值转换为SQLite中的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！