How to transform select statement to output with multiple columns

Question

I have the following select statement

SELECT 
  [Id],
  [Name],
  [Language],
  [Value]
FROM [dbo].[Translations]

The result:

Id  Name            Language Value
----------------------------------
1   Role - Singular en  Role
1   Role - Singular de  Role
1   Role - Singular nl  Rol
1   Role - Singular pl  Rola
2   Role - Plural   pl  Role
2   Role - Plural   nl  Rollen
2   Role - Plural   en  Roles
3   User - Singular en  User
3   User - Singular nl  Gebruiker
3   User - Singular de  Benutzer
3   User - Singular pl  Użytkownik
4   User - Plural   pl  Użytkownicy
4   User - Plural   nl  Gebruikers
4   User - Plural   en  Users

How can I transform to this output

Id  Name            English Dutch       Polish      German
------------------------------------------------------------
1   Role - Singular Role    Rol         Rola        Role
2   Role - Plural   Roles   Rollen      Role        NULL
3   User - Singular User    Gebruiker   Użytkownik  Benutzer
4   User - Plural   Users   Gebruikers  Użytkownicy NULL

Could you please help? I have not any code to show what I try, because I'm beginner in sql. There could be only 4 columns for languages (Englisn, Dutch, Polish, German)

[Cross Tabs and Pivots](http://www.sqlservercentral.com/articles/T-SQL/63681/) — Thom A, Oct 18 '18 at 13:33

score 1 · Accepted Answer · answered Oct 18 '18 at 13:43

You cam also use following CASE statement where the columns are limited in number and fixed

select
  [Id],
  [Name],
  max(English) as English,
  max(Dutch) as Dutch,
  max(Polish) as Polish,
  max(German) as German
from (
    SELECT 
      [Id],
      [Name],
      case when [Language] = 'en' then [Value] end as English ,
      case when [Language] = 'nl' then [Value] end as Dutch  ,
      case when [Language] = 'pl' then [Value] end as Polish ,
      case when [Language] = 'de' then [Value] end as German 
    FROM [dbo].[Translations]
) t
group by
  [Id],
  [Name]

How to transform select statement to output with multiple columns

1 Answers1