How to convert distinct field values to columns in SQLite?

Question

I have a table that looks like this:

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

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]

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...

Answer 1

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

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;

Answer 2

You can do same this way also :-

Set Nocount On;

Declare  @LanguageId        Int
        ,@Sql1              Varchar(Max)
        ,@SqlCols           Varchar(Max)
        ,@SqlUpdates        Varchar(Max)

If Object_Id('tempdb.dbo.#WidgetLanguage') Is Not Null Drop Table #WidgetLanguage;

If Object_Id('tempdb.dbo.#Widgets') Is Not Null Drop Table #Widgets;

Create Table #WidgetLanguage
(
     WidgetName         Varchar(100)
    ,LanguageId         Int
    ,Text               Varchar(100)
)

Create Table #Widgets
(
    WidgetName          Varchar(100)
)

Insert Into #WidgetLanguage(WidgetName,LanguageId,Text) Values
 ('lbNext',1031,'weiter')
,('lbNext',1033,'next')
,('btConnect',1031,'verbinden')
,('btConnect',1033,'connect')

Select   @LanguageId = Min(wl.LanguageId)
        ,@Sql1 = ''
        ,@SqlCols = ''
        ,@SqlUpdates = ''
From    #WidgetLanguage As wl With (Nolock)

Insert Into #Widgets(WidgetName)
Select  Distinct
        wl.WidgetName
From    #WidgetLanguage As wl With (Nolock)

;With AllLanguages As
(   
    Select  @LanguageId As LanguageId

    Union All

    Select  Top 1 wl.LanguageId
    From    (
                Select  Distinct
                        LanguageId
                From    #WidgetLanguage As wl With (Nolock)
            ) As wl
    Where   wl.LanguageId > @LanguageId
)

Select   @Sql1 = @Sql1 + '[' + Cast(al.LanguageId As Varchar(20)) + '],'
        ,@SqlCols = @SqlCols + ',[' + Cast(al.LanguageId As Varchar(20)) + '] Varchar(100)'
        ,@SqlUpdates = @SqlUpdates + ',w.[' + Cast(al.LanguageId As Varchar(20)) + '] = Isnull(t.[' + Cast(al.LanguageId As Varchar(20)) + '],'''')'
From    AllLanguages As al With (Nolock)


Select   @Sql1 = Substring(@Sql1, 0, Len(@Sql1))
        ,@SqlCols = 'Alter Table #Widgets Add ' + Substring(@SqlCols, 2, (Len(@SqlCols) + 1))
        ,@SqlUpdates = Substring(@SqlUpdates, 2, (Len(@SqlUpdates) + 1))

Exec (@SqlCols)

Select  @Sql1 = 'Update w Set ' + @SqlUpdates +
                'From   #Widgets As w ' +
                        'Join ' +
                        '(' +
                            'Select WidgetName,' + @Sql1 + ' From ' +
                                '(' +
                                    'Select wl.WidgetName,wl.Text,wl.LanguageId '+
                                    'From   #WidgetLanguage As wl With (Nolock) '+
                                ') As wl Pivot(Max(wl.Text) For wl.LanguageId In (' + @Sql1 + ')) t' +
                        ') As t On w.WidgetName = t.WidgetName '

----Print (@Sql1)
Exec (@Sql1)


Select  *
From    #Widgets As wl With (Nolock)