Help Needed in Joining Results

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  February 25, 2015 at 8:20 am

  #298042

  Hi,

  Below is my sample data

  Declare @sample1-2 table (name varchar(20),val1 int)

  insert into @sample1-2

  select 'Rob', 1 union all

  select 'Beat',2 union all

  select 'Crew', 3 union all

  select 'Read',4

  Declare @sample2 table (name varchar(20),val2 int)

  insert into @sample2

  select 'Rob', 100 union all

  select 'Beat',200 union all

  select 'Crew', 300 union all

  select 'Read',400

  Declare @sample3 table (name varchar(20),val3 int)

  insert into @sample3

  select 'Rob', 1000 union all

  select 'Beat',2000 union all

  select 'Crew', 3000 union all

  select 'Read',4000

  I want to join the results. Name is common,

  Expected Output:

  declare @Expectedoutput table(name varchar(20),val1 int,val2 int,val3 int,val4 int)

  insert into @Expectedoutput

  select 'Rob', 1,100,1000 union all

  select 'Beat',2,200,2000 union all

  select 'Crew',3,300,3000 union all

  select 'Read',4,400,4000

  select * from @Expectedoutput

  i tried with union and union all. but not producing expected result. Any idea how to achieve please

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  February 25, 2015 at 8:39 am

  #1779942

  SELECT COALESCE(s1.Name,s2.Name,s3.Name) AS name,

  s1.val1,

  s2.val2,

  s3.val3

  FROM @sample1-2 s1

  FULL OUTER JOIN @sample2 s2 ON s2.Name = s1.Name

  FULL OUTER JOIN @sample3 s3 ON s3.Name = COALESCE(s1.Name,s2.Name);

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  February 25, 2015 at 9:55 am

  #1779975

  Wonderful and appreciated your help. Worked like charm.