CODEWITHSUNDEEP
sqlsql-serversql-server-2008
JamesHussey
JamesHussey

Reputation: 23

Combine Two Tables in query

I have two Tables in a SQL 2008 database that I need to merge two column's when I run the query.

Table1 I need Emply_ID, Emply_Name, Emply_HomeDept, Emply_Shift

Table2 I need Emply_ID, Emply_OtherDept, Emply_AltShift.

What I'd to be able to do is produce a single row, combining HomeDept and OtherDept together and Shift and AltShift together. So I would end up with

00124|John Doe|Loading,Packing,Shipping|Shift1,Shift2,Shift3

Upvotes: 2

Views: 110

Answers (1)

Rafał Wojtaszek
Rafał Wojtaszek

Reputation: 668

This should meet your expectations:

SELECT t.EmplyId, 
       t.Emply_name, 
       CONCAT(t.Emply_HomeDept, ',' , x.otherDept) AS FullDept,
       CONCAT(t.Emply_Shift, ',' , x.AltShift) AS FullShift,
FROM
    Table1 t
    LEFT JOIN (
    SELECT t2.EmplyId,
           (SELECT t3.OtherDept + ',' AS [text()] 
            FROM table2 t3
            WHERE t3.EmplyId = t2.EmplyId
            FOR XML PATH ('')
           ) AS otherDept,
           (SELECT t4.AltShift + ',' AS [text()] 
            FROM table2 t4
            WHERE t4.EmplyId = t2.EmplyId
            FOR XML PATH ('')
           ) AS AltShift           
    FROM
        Table2 t2 ) x
    ON t.Emply_Id = x.EmplyId

Upvotes: 3

Related Questions