Make 1 row result from two result using SQL Server

Question

Using SQL Server, is it possible to create a one row result from two rows? See the following:

Sample Data:

+-----+-----------+----------------+------------+---------------------+
| PC  | DriveName | AvailableSpace | TotalSpace |    PostingDtime     |
+-----+-----------+----------------+------------+---------------------+
| 111 | C:\       | 241 GB         | 405 GB     | 03/01/2024 23:00:00 |
| 111 | D:\       | 507 GB         | 546 GB     | 03/01/2024 23:00:00 |
| 111 | C:\       | 241 GB         | 405 GB     | 03/01/2024 0:00:00  |
| 111 | D:\       | 507 GB         | 546 GB     | 03/01/2024 0:00:00  |

Desired Results

+-----+-----------+----------------+------------+-----------+
| PC  | DriveName | AvailableSpace | TotalSpace | DriveName |
+-----+-----------+----------------+------------+-----------+
| 111 | C:\       | 241 GB         | 405 GB     | D:\       |
| 111 | C:\       | 241 GB         | 405 GB     | D:\       |
+-----+-----------+----------------+------------+-----------+

I tried to use the pivot and cross join table as suggestion across the web, but I can't see similar answer from the web also.

Answer 1

You can self join to get these results by joining on PC and PostingDTime, then restricting each table alias for the drive you are after. Also, I'm guessing that there are more columns desired and that was cut off in the original screenshot from which the data was transcribed.

SELECT t1.PC, t1.DriveName, t1.AvailableSpace, t1.TotalSpace, t2.DriveName, t2.AvailableSpace, t2.TotalSpace
FROM yourtable as t1
    LEFT OUTER JOIN yourtable as t2
        ON t1.PC = t2.PC
        AND t1.PostingDtime = t2.PostingDTime
        AND t2.Drive = 'D:\'
WHERE t1.Drive = 'C:\'