sql - How to merge two queries into one

Question

How can I join these 2 SQL statements? I want the columns of the first and the columns of the second to appear together as one SQL query.

SELECT  
    E.tbl1_ORG AS Organización, E.tbl1_CODE AS [Orden de Trabajo], 
    E.tbl1_OBJECT AS Equipo, O.tbl3_POSITION AS Posicion, 
    E.tbl1_JOBTYPE AS [Tipo de Trabajo], 
    E.tbl1_DESC AS [Descripcion OT], E.tbl1_WORKADDRESS AS Comentarios, 
    E.tbl1_REQM AS Error, B.tbl2_PERSON AS Trabajador, 
    B.tbl2_ENTERED AS Fecha, B.tbl2_HOURS AS Horas
FROM    
    dbo.table1 AS E 
INNER JOIN
    dbo.table2 AS B ON E.tbl1_CODE = B.tbl2_EVENT 
INNER JOIN
    dbo.table3 AS O ON O.tbl3_CODE = E.tbl1_OBJECT
WHERE   
    E.tbl1_JOBTYPE IN ('PM', 'CM', 'PMM') and
    E.tbl1_ORG = #PROMPT('Organización')# and
    B.tbl2_ENTERED between #PROMPT('Fecha_Inicio')# and #PROMPT('Fecha_Final')# and 
    (E.tbl1_REQM = #PROMPT('Error')# OR #PROMPT('Error')# = '%') and
    (E.tbl1_OBJECT = #PROMPT('Equipo')# OR #PROMPT('Equipo')# = '%') and
    (O.tbl3_POSITION = #PROMPT('Posicion')# OR #PROMPT('Posicion')# = '%')

And:

SELECT  
    tbl2_event 'Orden de Trabajo',
    STUFF((SELECT ', ' + CAST(tbl2_person AS VARCHAR(100)) [text()]    
           FROM table2 
           WHERE tbl2_event = t.tbl2_event
           FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,' ') Empleados, 
    STUFF((SELECT ', ' + CAST(tbl2_hours AS VARCHAR(100)) [text()]       
           FROM table2 
           WHERE tbl2_event = t.tbl2_event
           FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 1, ' ') Horas
FROM table2 t
GROUP BY tbl2_event

Both work perfectly on their own, but I don't know how to merge them.

Answer 1

Add ROW_NUMBER to each query and then FULL JOIN them together. You'll need to decide on the ordering of rows in each query.

WITH
CTE1
AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY ...) AS rn,
        E.tbl1_ORG AS Organización, E.tbl1_CODE AS [Orden de Trabajo], 
        E.tbl1_OBJECT AS Equipo, O.tbl3_POSITION AS Posicion, 
        E.tbl1_JOBTYPE AS [Tipo de Trabajo], 
        E.tbl1_DESC AS [Descripcion OT], E.tbl1_WORKADDRESS AS Comentarios, 
        E.tbl1_REQM AS Error, B.tbl2_PERSON AS Trabajador, 
        B.tbl2_ENTERED AS Fecha, B.tbl2_HOURS AS Horas
    FROM
        dbo.table1 AS E 
    INNER JOIN
        dbo.table2 AS B ON E.tbl1_CODE = B.tbl2_EVENT 
    INNER JOIN
        dbo.table3 AS O ON O.tbl3_CODE = E.tbl1_OBJECT
    WHERE   
        E.tbl1_JOBTYPE IN ('PM', 'CM', 'PMM') and
        E.tbl1_ORG = #PROMPT('Organización')# and
        B.tbl2_ENTERED between #PROMPT('Fecha_Inicio')# and #PROMPT('Fecha_Final')# and 
        (E.tbl1_REQM = #PROMPT('Error')# OR #PROMPT('Error')# = '%') and
        (E.tbl1_OBJECT = #PROMPT('Equipo')# OR #PROMPT('Equipo')# = '%') and
        (O.tbl3_POSITION = #PROMPT('Posicion')# OR #PROMPT('Posicion')# = '%')
)
,CTE2
AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY ...) AS rn,
        tbl2_event 'Orden de Trabajo',
        STUFF((SELECT ', ' + CAST(tbl2_person AS VARCHAR(100)) [text()]    
               FROM table2 
               WHERE tbl2_event = t.tbl2_event
               FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,' ') Empleados, 
        STUFF((SELECT ', ' + CAST(tbl2_hours AS VARCHAR(100)) [text()]       
               FROM table2 
               WHERE tbl2_event = t.tbl2_event
               FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 1, ' ') Horas
    FROM table2 t
    GROUP BY tbl2_event
)
SELECT ...
FROM
    CTE1 FULL JOIN CTE2 ON CTE1.rn = CTE2.rn
ORDER BY ...
;

If you have more than 2-3 tables to join like this FULL JOIN would quickly become ugly and slow. Have a look at my answer for a similar question for alternative solution: best way to "glue" columns together