CODEWITHSUNDEEP
sqlsql-server
Jon Warren
Jon Warren

Reputation: 857

SQL Server, joining all values in column with some values of another

I'm pretty bad at explaining, so I'll try and let my examples do most of the talking. Let's say I have a table like so:

          dbo.ExampleTable
===================================


  ID     Year     Data1     Data2
====== ======== ========= =========
  12     2016      FOO       BAR
  13     2016      FOO       MAN
  14     2016      SAW       BAR

  20     2017      FOO       BAR
  21     2017      FOO       MAN
  27     2017      SAW       BAR
  29     2017      CHU       CAR

  44     9999      FOO       BAR
  48     9999      FOO       MAN
  51     9999      SAW       BAR
  52     9999      CHU       CAR

Some notes:

I want to create a table from that data that looks like this:

  ID_9999     ID_2016     ID_2017  
=========== =========== ===========
     44          12          20
     48          13          21
     51          14          27
     52         NULL         29

So essentially, for every unique pairing of Data1 and Data2 where Year=9999, I want to create a row which contains the ID of that pairing where Year=9999, as well as the ID for the pairings where Year=2016 and also Year=2017. Additionally, if either 2016 or 2017 do not containing that Data pairing, I want their value as NULL.

This is the query I've got so far:

SELECT      tbl9999.ID       ID_9999,
            tbl2016.ID       ID_2016,
            tbl2017.ID       ID_2017

FROM        dbo.ExampleTable tbl9999

LEFT JOIN   dbo.ExampleTable tbl2016
ON          tbl9999.Data1 = tbl2016.Data1
AND         tbl9999.Data2 = tbl2016.Data2

LEFT JOIN   dbo.ExampleTable tbl2017
ON          tbl9999.Data1 = tbl2017.Data1
AND         tbl9999.Data2 = tbl2017.Data2

WHERE       tbl9999.Year=9999
AND         tbl2016.Year=2016
AND         tbl2017.Year=2017

This seems to work mostly fine, however it will generate a table like this:

  ID_9999     ID_2016     ID_2017  
=========== =========== ===========
     44          12          20
     48          13          21
     51          14          27

*Notice that it's missing the row with the null value in my example above. Is there any way to change my query to include that null value such that I have it in my example?

Please let me know if I'm missing any information or need anything clarified. Thanks in advance!

EDIT: I was able to find an answer on my own! This is the code I used to achieve my desired result:

SELECT      [9999] [ID_9999],
            [2016] [ID_2016],
            [2017] [ID_2017]
FROM        dbo.ExampleTable
PIVOT       (MAX([ID]) FOR [Year] IN ([2016],[2017],[9999])) [x]
ORDER BY    ID_9999

Upvotes: 2

Views: 435

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this in multiple ways. Conditional aggregation seems simple enough:

select max(case when year = 2016 then id end) as id_2016,
       max(case when year = 2017 then id end) as id_2017,
       max(case when year = 9999 then id end) as id_9999

from (select t.*, row_number() over (partition by year order by id) as seqnum
      from dbo.ExampleTable t
     ) t
group by seqnum
order by seqnum;

Upvotes: 3

Related Questions