SQL Server 2008 similar to group_concat topic fill distinct colums from two separate tables

Sorry... first time here and amateur...

I have two different tables (contact_data_a and contact_data_b) from two different divisions as follows:

contact_data_a      
id  customer    contact
11200   Müller KG   Hans
11201   Huber GmbH  Patrick
11203   Gruber GmbH Manu
11205   Meyer GmbH  Manu

contact_data_b      
id  customer    contact
11200   Müller E.   Peter
11202   Schubert AG Louis
11204   E.Schmidt   Louis
11205   Mayer GmbH  Peter

What I would like to have in the end is something like this:

contact_data_all            
id  customer    contact_a   contact_b
11200   Müller KG   Hans    Peter
11201   Huber GmbH  Patrick 0
11202   Schubert AG 0   Louis
11203   Gruber GmbH Manu    0
11204   E. Schmidt  0   Louis
11205   Meyer GmbH  Manu    Peter

"id" is clear and distinct, but names in column "customer" might vary (incl. misspellings). This is no problem. Information could come from either table. My problem are the contact columns. Contacts from list contact_data_a should appear in column contact_a (or Null if they do not exist) and contacts from list contact_data_b should appear in column contact_b (or Null).

A friend said I might use

    `SELECT id, customer, GROUP_CONCAT(contact_a) as contact_a,GROUP_CONCAT(contact_b) as contact_b FROM
(SELECT id, customer, contact_a, null as contact_b FROM contact_data_a
UNION
SELECT id, customer, null as contact_a, contact_b FROM contact_data_b) 
GROUP BY id ORDER BY id`

But I only have SQL 2008, so CONCAT is not available yet. Thank you in advance for any help or idea!!!

Answers


Try this one -

SELECT
      id
    , customer
    , MAX(contact_a) AS contact_a
    , MAX(contact_b) AS contact_b 
FROM (
    SELECT id, customer, contact_a, '0' AS contact_b 
    FROM contact_data_a

    UNION ALL

    SELECT id, customer, '0' AS contact_a, contact_b 
    FROM contact_data_b
) t
GROUP BY id, customer
ORDER BY id

Need Your Help

Mock Runtime.getRuntime()?

java unit-testing junit easymock runtime.exec

Can anyone make any suggestions about how best to use EasyMock to expect a call to Runtime.getRuntime().exec(xxx)?

JQuery UI inline DatePicker loses date after ASP.NET postback

asp.net jquery-ui jquery-ui-datepicker

I have an inline DatePicker (Week-Picker) initiaized as follows, which on Select fires a ASP.NET click event. It functions fine and my ASP code does it's part, however if I select a date for ex: af...