Sql-server – How could i join this two queries

join;pivotsql server

I have two queries.

The first one is:

    WITH Numbered AS
(
SELECT
        B.id,
        B.masahat,
        B.tozihat,
        mahsol_name = G.mahsolname,
        ghate_code = G.ghate_code,
        M.cheshme,
        rn = ROW_NUMBER() OVER (
            PARTITION BY B.id
            ORDER BY G.id)
    FROM bahrebardarighate AS B
    JOIN ghatemanabeabi AS G
        ON B.id = G.bahrebardarighate_id
    JOIN manabeabi AS M
        ON M.id = G.manabeabi_id
)
SELECT
    N.id,
    N.masahat,
    N.tozihat,
    N.cheshme,
    [mahsol_name_1] = MAX(CASE WHEN N.rn = 1 THEN N.mahsol_name ELSE NULL END),
    [ghate_code_1] = MAX(CASE WHEN N.rn = 1 THEN N.ghate_code ELSE NULL END),
    [mahsol_name_2] = MAX(CASE WHEN N.rn = 2 THEN N.mahsol_name ELSE NULL END),
    [ghate_code_2] = MAX(CASE WHEN N.rn = 2 THEN N.ghate_code ELSE NULL END),    
    [mahsol_name_3] = MAX(CASE WHEN N.rn = 3 THEN N.mahsol_name ELSE NULL END),
    [ghate_code_3] = MAX(CASE WHEN N.rn = 3 THEN N.ghate_code ELSE NULL END)
    -- Repeat the pattern up to 12
FROM Numbered AS N
GROUP BY 
    N.id, -- key column
    N.masahat,
    N.tozihat,
    N.cheshme

The second query is:

    WITH Numbered2 AS
(
SELECT
        A.id,
        A.bahrebardarighate_id,
        fullname = S.fullname,
        nationalcode = S.nationalcode,
        rn = ROW_NUMBER() OVER (
            PARTITION BY A.id
            ORDER BY S.id)
    FROM ghate AS A
    JOIN shoraka AS S
        ON A.id = s.ghate_id
)
SELECT
    N.id,
    N.bahrebardarighate_id,
    [fullname_1] = MAX(CASE WHEN N.rn = 1 THEN N.fullname ELSE NULL END),
    [nationalcode_1] = MAX(CASE WHEN N.rn = 1 THEN N.nationalcode ELSE NULL END),
    [fullname_2] = MAX(CASE WHEN N.rn = 2 THEN N.fullname ELSE NULL END),
    [nationalcode_2] = MAX(CASE WHEN N.rn = 2 THEN N.nationalcode ELSE NULL END),    
    [fullname_3] = MAX(CASE WHEN N.rn = 3 THEN N.fullname ELSE NULL END),
    [nationalcode_3] = MAX(CASE WHEN N.rn = 3 THEN N.nationalcode ELSE NULL END)
    -- Repeat the pattern up to 12
FROM Numbered2 AS N
GROUP BY 
    N.id, -- key column
    N.bahrebardarighate_id

How could i join them in one query?

Best Answer

--Declare both of your CTE first
WITH Numbered AS
(
SELECT
    B.id,
    B.masahat,
    B.tozihat,
    mahsol_name = G.mahsolname,
    ghate_code = G.ghate_code,
    M.cheshme,
    rn = ROW_NUMBER() OVER (
        PARTITION BY B.id
        ORDER BY G.id)
FROM bahrebardarighate AS B
JOIN ghatemanabeabi AS G
    ON B.id = G.bahrebardarighate_id
JOIN manabeabi AS M
    ON M.id = G.manabeabi_id
),--Separating the two CTE declarations
Numbered2 AS
(
SELECT
    A.id,
    A.bahrebardarighate_id,
    fullname = S.fullname,
    nationalcode = S.nationalcode,
    rn = ROW_NUMBER() OVER (
        PARTITION BY A.id
        ORDER BY S.id)
FROM ghate AS A
JOIN shoraka AS S
    ON A.id = s.ghate_id
)


--Then your select/UNION statement goes here, ensuring the two selects have the same number of columns and datatypes.
Related Question