CODEWITHSUNDEEP
sqlsql-server
ipedrosa
ipedrosa

Reputation: 28

Two rows with the same id and two different values, getting the second value into another column

I have two rows with the same id but different values. I want a query to get the second value and display it in the first row.

There are only two rows for each productId and 2 different values. I've tried looking for this for the solution everywhere.

What I have, example: 

+-----+-------+
| ID  | Value |
+-----+-------+
| 123 |     1 |
| 123 |     2 |
+-----+-------+

What I want

+------+-------+---------+
|  ID  | Value | Value 1 |
+------+-------+---------+
| 123  |     1 |       2 |
+------+-------+---------+

Upvotes: 0

Views: 1488

Answers (3)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

May be you may try this

DECLARE @T TABLE
(
    Id INT,
    Val INT
)

INSERT  INTO @T
VALUES(123,1),(123,2),
(456,1),(789,1),(789,2)

;WITH CTE
AS
(
    SELECT
        RN = ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Val),
        *
        FROM @T
)
SELECT
    *
    FROM CTE
    PIVOT
    (
        MAX(Val)
        FOR
        RN IN
        (
            [1],[2]--Add More Numbers here if there are more values
        )
    )Q

Upvotes: 2

Revealing Light
Revealing Light

Reputation: 36

Not sure whether order matters to you. Here is one way:

SELECT MIN(Value), MAX(Value), ID
FROM Table
GROUP BY ID;

Upvotes: 2

Aaron Dietz
Aaron Dietz

Reputation: 10277

This is a self-join:

SELECT a.ID, a.Value, b.Value
FROM table a
JOIN table b on a.ID = b.ID
            and a.Value <> b.Value

You can use a LEFT JOIN instead if there are IDs that only have one value and would be lost by the above JOIN

Upvotes: 2

Related Questions