0

I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas.

Basically, what I want to do seems like a UNION where records are "collapsed" in a sense to remove NULL values (where say, the ID and DATE are the same). I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to produce the desired result. It seems like maybe MSSQL's MERGE is maybe what I'm looking for? It seems like that operation is meant for table creation, however.

My end goal is basically to create a view from the result. All I really need is the SELECT statement that allows me to do further manipulation.

To help visualize the problem, let's say I have the below tables:

+----+--------+-------------+---------------+
| ID | Date   | FloorRating | CeilingRating |
+----+--------+-------------+---------------+
| 1A | 1/1/18 | 9           | 6             |
+----+--------+-------------+---------------+
| 1A | 1/1/19 | 9           | 7             |
+----+--------+-------------+---------------+
| 3D | 2/2/19 | 5           | 8             |
+----+--------+-------------+---------------+
| 3D | 3/3/19 | 4           | 9             |
+----+--------+-------------+---------------+


+----+--------+--------------+-----------+
| ID | Date   | WindowRating | FanRating |
+----+--------+--------------+-----------+
| 1A | 1/1/18 | 4            | 7         |
+----+--------+--------------+-----------+
| 1A | 2/2/19 | 3            | 7         |
+----+--------+--------------+-----------+
| 3D | 2/2/19 | 9            | 4         |
+----+--------+--------------+-----------+
| 3D | 8/8/19 | 1            | 3         |
+----+--------+--------------+-----------+

What I want to end up with is something like this, where all possible ID-Date combinations are accounted for and field values are filled in where possible:

+----+--------+-------------+---------------+--------------+-----------+
| ID | Date   | FloorRating | CeilingRating | WindowRating | FanRating |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 1/1/18 | 9           | 6             | 4            | 7         |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 1/1/19 | 9           | 7             |              |           |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 2/2/19 |             |               | 3            | 7         |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 2/2/19 | 5           | 8             | 9            | 4         |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 3/3/19 | 4           | 9             |              |           |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 8/8/19 |             |               | 1            | 3         |
+----+--------+-------------+---------------+--------------+-----------+

I appreciate any help or tips you can provide!

1

I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to produce the desired result

FULL JOIN works fine. Maybe you were joining on the wrong predicate or didn't coalesce the results from the ID and Date columns (done with ISNULL below)?

db <> fiddle

SELECT ISNULL(T1.[ID], T2.[ID]) AS [ID],
       ISNULL(T1.Date, T2.Date) AS Date,
       [FloorRating],
       [CeilingRating],
       [WindowRating],
       [FanRating]
FROM   Table1 T1
       FULL JOIN Table2 T2
              ON T1.[ID] = T2.[ID]
                 AND T1.[Date] = T2.[Date]
ORDER  BY [ID],
          Date 
| improve this answer | |
  • Ah thank you very much! Yes, I was missing the coalesce. How would this change if I were doing with with 3 tables? I realize isnull only takes two arguments, so I would need to use COALESCE in that scenario. I think this is enough if I just have to do multiple joins. Thank you so much! – tapehead May 5 at 13:21
  • In that case you would add the join like FULL JOIN Table3 T3 ON ISNULL(T1.[ID], T2.[ID]) = T2.[ID] AND ISNULL(T1.Date, T2.Date) and use COALESCE in the SELECT list as that accepts more than 2 arguments – Martin Smith May 5 at 13:25
  • Ah yes, experimented a bit and now everything working beautifully. Thanks a lot! – tapehead May 5 at 13:39

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.