0

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?

3
  • With a UNION statement
    – paparazzo
    Oct 14 '16 at 13:35
  • @Paparazzi I test it but it has error...can you show me how do it Oct 14 '16 at 13:39
  • It's erroring because you have a different number of columns in one query vs. the other. They need to have the same number of columns.
    – SQLDevDBA
    Oct 14 '16 at 13:43
0
--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.
2
  • You're welcome. Sorry I didn't send the complete query but I figured you can take it from there.
    – SQLDevDBA
    Oct 14 '16 at 14:14
  • you help me so much..I would like to learn more about sql an sql server,can you introduced me a book or website? Oct 14 '16 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.