CODEWITHSUNDEEP
t-sql
RockGuitarist1
RockGuitarist1

Reputation: 115

Using same column twice, but 1st column dups are removed

I have 2 separate Select Statements. Both of them select PrimaryID, but they also Select columns that aren't shared, like PrimaryID. I am using an Inner Join to get my results back that look like this:

PrimaryID | col2 | col3 | PrimaryID | col4 | col5
    1         2      3        1         2      3
    1         3      4        1         3      4
    1         5      6        1         5      6
    2         3      4        2         3      4
    2         5      6        2         5      6
    2         7      8        2         7      8

What is wanted is this:

PrimaryID | col2 | col3 | PrimaryID | col4 | col5
    1         2      3        1         2      3
                              1         3      4
                              1         5      6
    2         3      4        2         3      4
                              2         5      6
                              2         7      8

Is there anyway that I can achieve this? Thanks.

QUERY:

Select * from
    (SELECT primaryid, col2, col3
    from g.table
    left join (random stuff)
    WHERE DATEPART(m, CreateDateTime) = DATEPART(m, DATEADD(m, -1, getdate()))
    AND DATEPART(yyyy, CreateDateTime) = DATEPART(yyyy, DATEADD(m, -1, getdate()))) t1
inner join
    (select primaryid, col4, col5
    from g.table
    where primaryid in (select primaryid from g.table) and col5 = 0) t2
    on t1.primaryid = t2.primaryid
    order by t1.col2

Upvotes: 0

Views: 49

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Your query is syntactically not correct, so my suggestion isn't either, but you'll get the idea: Use ROW_NUMBER with an OVER(PARTITION BY ...) to get numbered sub-sets. Then use CASE to force the needed values only in lines where your number is "1". Other lines return an empty string or NULL.

Attention: I changed your two identically named primaryid columns to ID1 and ID2:

WITH Numbered AS
(
    Select *
          ,ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY col2,col3) AS SortNumber
    from
        (SELECT primaryid AS ID1, col2, col3
        from g.table
        left join (random stuff)
        WHERE DATEPART(m, CreateDateTime) = DATEPART(m, DATEADD(m, -1, getdate()))
        AND DATEPART(yyyy, CreateDateTime) = DATEPART(yyyy, DATEADD(m, -1, getdate()))) t1
    inner join
        (select primaryid AS ID2, col4, col5
        from g.table
        where primaryid in (select primaryid from g.table) and col5 = 0) t2
        on t1.ID1 = t2.ID2
)
SELECT CASE WHEN SortNumber=1 THEN ID1 ELSE '' END AS ID1
      ,CASE WHEN SortNumber=1 THEN col2 ELSE '' END AS col2
      ,CASE WHEN SortNumber=1 THEN col3 ELSE '' END AS col3
      ,ID2,col4,col5
FROM Numbered

Upvotes: 1

Related Questions