CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Rohan
Rohan

Reputation: 177

Combining Multiple results without duplication

enter image description here

I tried following query but i am getting repeated rows, i want both results extra rows should be marked Null.

WITH E_CTE (Column1,Column2,Column3)AS
(Select Column1,Column2,Column3 From Table1 as c1 )
Select x.*,y.*  
from  (SELECT * from E_CTE Where column3='Yes') as x,
      (SELECT * FROM E_CTE Where column3='No') as y

Upvotes: 1

Views: 37

Answers (2)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

And another solution is using FULL JOIN like this -

Schema

DECLARE @T TABLE (Column1 int, Column2 VARCHAR(10), Column3 VARCHAR(10));
INSERT @T
    VALUES (1, 'A', 'YES'), (2, 'B', 'YES'), (3, 'C', 'NO'), (4, 'D', 'NO'), (5, 'E', 'NO');

Query

;WITH E_CTE
AS 
(   SELECT
        Column1
        ,Column2
        ,Column3
        ,rn = ROW_NUMBER() OVER (PARTITION BY c1.Column3 ORDER BY c1.Column1)
    FROM @T AS c1
)
SELECT
    Column1Yes
    ,Column2Yes
    ,Column1No
    ,Column2No
FROM (SELECT
        Column1 AS Column1Yes
        ,Column2 AS Column2Yes
        ,rn
    FROM E_CTE
    WHERE column3 = 'Yes') AS x
FULL JOIN (SELECT
        Column1 AS Column1No
        ,Column2 AS Column2No
        ,rn
    FROM E_CTE
    WHERE column3 = 'No') AS y
    ON x.rn = y.rn

Output

Column1Yes  Column2Yes  Column1No   Column2No
    1            A          3           C
    2            B          4           D
    NULL         NULL       5           E

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

Using windowed function ROW_NUMBER and conditional aggregation:

WITH cte AS (
  SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY Column3 ORDER BY Column1)
  FROM #Table1
)
SELECT Column1Yes = MAX(CASE WHEN Column3 = 'yes' THEN Column1 END),
       Column2Yes = MAX(CASE WHEN Column3 = 'yes' THEN Column2 END),
       Column1No  = MAX(CASE WHEN Column3 = 'no' THEN Column1 END),
       Column2No  = MAX(CASE WHEN Column3 = 'no' THEN Column2 END)
FROM cte
GROUP BY rn;

LiveDemo

Output:

╔════════════╦════════════╦═══════════╦═══════════╗
║ Column1Yes ║ Column2Yes ║ Column1No ║ Column2No ║
╠════════════╬════════════╬═══════════╬═══════════╣
║          1 ║ A          ║         3 ║ C         ║
║          2 ║ B          ║         4 ║ D         ║
║            ║            ║         5 ║ E         ║
╚════════════╩════════════╩═══════════╩═══════════╝

Upvotes: 1

Related Questions