Merge multiple rows of same ID into Single row

• sasil

  Old Hand

  Points: 399

  More actions

  February 19, 2015 at 11:09 pm

  #304400

  Hi All,

  I need the requirements of merging multiple rows of same ID as single row.

  My Table Data:

  IDLanguage1Language2Language3Language4

  1001NULL JAPANESENULL NULL

  1001SPANISH NULL NULL NULL

  1001NULL NULL NULL ENGLISH

  1001NULL NULL RUSSIAN NULL

  Required Output Should be,

  IDLanguage1Language2Language3Language4

  1001SPANISH JAPANESERUSSIAN ENGLISH

  Please help me to achieve this output.

  Tried grouping but its not working also producing the same result.

  Thanks.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  February 19, 2015 at 11:25 pm

  #1778857

  You tried grouping? Keep at it... that was the right direction.

  SELECT ID

  , MAX(Lang1) AS Language1

  ,MAX(Lang2) AS Language2

  ,Max(Lang3) As language3

  ,MAX(Lang4) AS Language4

  FROM

  (SELECT 1001 AS ID, Null as Lang1,'Japanese' as Lang2,Null As Lang3, Null AS Lang4

  UNION ALL

  SELECT 1001,'Spanish',Null,Null,Null

  UNION ALL

  SELECT 1001,Null,Null,Null,'English'

  UNION ALL

  SELECT 1001,'Spanish','Japanese','Russian','English') d

  GROUP BY Id;