How to merge records in SQL [closed]

Question

I have a table with multiple records for each ID.

For example, there may be three or more records for one Class ID): one record with a status of "approved" and two more records with status NULL, or one record with the approved field populated with some numerical value and another two records will approved set to null, or one record with the requested field set to some numerical value and two other records with requested set to be null.

As output, I want one record with status "approved", and with approved and requested populated with their numerical values.

The question via an example:

Now, I want a single record with column T approved and COLUMN W,X WITH 12 VALUE

EXAMPLE:

Answer 1

My guess is the question is something like:

I have a table that looks something like this:

+----+----------+----------+---------+
| ID |  Status  | Approved | Request |
+----+----------+----------+---------+
|  1 | NULL     | 5        | NULL    |
|  1 | Approved | NULL     | NULL    |
|  1 | NULL     | NULL     | 10      |
+----+----------+----------+---------+

And you are looking to combine all these rows into just the NON-Null values for each column. Something like:

+----+----------+----------+---------+
| ID |  Status  | Approved | Request |
+----+----------+----------+---------+
|  1 | Approved |        5 |      10 |
+----+----------+----------+---------+

For the purpose of the examples I will show below I have created the below table. (For the sake of cleanup ease on my side I am using Table Variables instead of a physical/temp table.)

DECLARE @Table TABLE
(
    ID INT,
    [Status] VarChar(20) NULL,
    Approved INT NULL,
    Request INT NULL
)

INSERT INTO @Table (ID, [Status], Approved, Request)
VALUES
(1, NULL, 5, NULL),
(1, 'Approved', NULL, NULL),
(1, NULL, NULL, 10)

There are a two ways to achieve this result that I am aware of:

1) Using Sub Queries in the SELECT statement. Something like below:

SELECT T.ID,
T.[Status],
(
    SELECT TOP (1) temp1.Approved
    FROM @Table temp1
    WHERE temp1.ID = T.ID
    AND temp1.Approved IS NOT NULL
    --If Needed You can add additional WHERE clauses
    --Or you can add some kind of ORDER clause to get the value you want
) AS Approved,
(
    SELECT TOP (1) temp1.Request
    FROM @Table temp1
    WHERE temp1.ID = T.ID
    AND temp1.Request IS NOT NULL
    --If Needed You can add additional WHERE clauses
    --Or you can add some kind of ORDER clause to get the value you want
) AS Request
FROM @Table T
WHERE ID = 1
AND [Status] = 'Approved'
--If Needed You can add additional WHERE clauses

We are simply running a second and third select, selecting a single value (the TOP (1) to avoid an error where a subquery returns more than one result), and having that display as the result for that column. The benefit of this option is it guarantees that we only get one row per instance of something in the table WHERE ID = 1 AND [Status] = 'Approved'.

2) Another option I am aware of is using additional JOINS. Something like below:

SELECT T.ID,
T.[Status],
App.Approved,
Req.Request
FROM @Table T
    LEFT OUTER JOIN @Table App
        ON App.ID = T.ID
        AND App.Approved IS NOT NULL
    LEFT OUTER JOIN @Table Req
        ON Req.ID = T.ID
        AND Req.Request IS NOT NULL
WHERE T.ID = 1
AND T.[Status] = 'Approved'

This approach allows you to get more than just a single value for that row. (Say there was some additional 5th column and you wanted that value from the "Request" row. You can just add a Req.[ColumnName] in the SELECT). This does have a possibility for returning multiple rows per ID = 1 AND [Status] = 'Approved' combination, if there are more than one row with ID = 1 AND Request IS NOT NULL OR ID = 1 and Approved IS NOT NULL.

There is a 3rd option using the XML STUFF:

Which allows you to combine multiple rows of the same column into a single "cell" using concatenation. This is explained in great detail via this Stack Overflow Question.

Hopefully that give you what you were looking for.

Answer 2

Example: There will be 3 or more records for 1 id (Class ID) One record with status field approved and other 2 records will be null One record with approved field ='some numerical value' and other 2 records will be null One record with requested field='some numerical value' and other 2 records will be null

This is pretty straightforward, assuming that what you said here is what you meant. (Please include examples next time).

SELECT 
    Class_ID,
    Max(Status) as Status,
    Max(Requested) as Requested,
    Max(Approved) as Approved
FROM MyDataTable
GROUP BY Class_ID