sql - Join language specific names in one row -

sql - Join language specific names in one row -

given next tables:

materials

+-----------------+-----------------+ |material |name | +-----------------------------------+ |00000001 |material number 1| |00000002 |material number 2| +-----------------+-----------------+

material names

+---------+---------+-------------+ |material |language |name | +---------------------------------+ |00000001 |de |mat. nummer 1| |00000001 |fr |matérial 1 | |00000002 |sp |mat. 1 | +---------+---------+-------------+

how can create sql statement (mssql 2005 flavor) homecoming info follows:

+---------+---------------+---------------+----------+----------+ |material |name_en |name_de |name_fr |name_sp | +---------------------------------------------------------------+ |00000001 |material number|1 mat. nummer 1|matérial 1|null | |00000002 |material number|2 null |null |mat. 1 | +---------+---------------+---------------+----------+----------+

i tried several approaches using case when, is not null, , like. resulted in single rows 1 column filled, if there more names available material.

what can accomplish this?

you can join , conditional aggregation:

select m.materialid, m.name name_en, max(case when nm.language = 'de' nm.name end) name_de, max(case when nm.language = 'fr' nm.name end) name_fr, max(case when nm.language = 'sp' nm.name end) name_sp materials m bring together materialnames nm on m.materialid = nm.materialid grouping m.materialid, m.name;

sql sql-server tsql sql-server-2005