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:

enter image description here

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

EXAMPLE: enter image description here

closed as unclear what you're asking by kevinsky, Max Vernon, mustaccio, LowlyDBA, Marcello Miorelli Jun 19 at 22:39

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    You should take a look at how to create a minimal, complete, and verifiable example then add the table definition, sample data, and desired output to your question. Also, please take the tour! – Max Vernon Jun 17 at 15:02
  • 1
    You talk about status, approved and requested but your example question has column headers like T, W, X. You haven't explained how the data might look for 4 records for same class ID - what happens if 1 record has a number in column W and another record has a different number in column W? Your rules are not clear. You're also the second person to post exactly the same example question - might this be a study assignment? What steps have you taken to try and solve the problem, so we can help you where you're stuck? – youcantryreachingme Jun 18 at 1:04

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.)

    ID INT,
    [Status] VarChar(20) NULL,
    Approved INT NULL,
    Request INT NULL

INSERT INTO @Table (ID, [Status], Approved, Request)
(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 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
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:

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
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.

  • My guess is the question comes from a study assignment - as someone else posted exactly the same wording and screenshot too. – youcantryreachingme Jun 18 at 1:01

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).

    Max(Status) as Status,
    Max(Requested) as Requested,
    Max(Approved) as Approved
FROM MyDataTable
  • Note that, when there are multiple records with values, the highest value will be selected, which is somewhat arbitrary. The OP's example may be optimistic regarding data cleanliness. – Jon of All Trades Jun 17 at 18:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.