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.

Please post your sample data and desired results as text, not screenshots. You can use reddit's table markdown or a site like ozh.github.io/ascii-tables to format for the question. — JNevill
– JNevill, Commented Mar 1, 2024 at 17:44
And in what way do you want to merge these rows or decide which one to show? For example, why do you show both entries for C:\ with 241GB, and a second column DriveName with D:\ in it, instead of two entries, one for C:\ and one for D:\ ? — Mark Rotteveel
– Mark Rotteveel, Commented Mar 1, 2024 at 17:50

JNevill · Accepted Answer · 2024-03-01 17:52:24Z

0

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:\'

answered Mar 1, 2024 at 17:52

JNevill

50.6k4 gold badges46 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Make 1 row result from two result using SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related