Move the data from different rows with same ID in same rows but different column in sql

Question

I have a data like

  ID   ded1   ded2   ded3   ded4 
  ------------------------------
  1    2
  1    3
  1    4 

I want:

ID   ded1   ded2   ded3   ded4
------------------------------
1    2      3      4

ded4 is blank since there is 3 values, if 4th value was there then ded4 fills up

Answer 1

While @ConsiderMe answer is quite correct, you can also do 4 joins if you have issues with the group by or there are additional columns you want to include from the source table. This is sometime the only viable solution. (BTW a pivot it basically the same as this code).

This code is slight slower than @CondiderMe's answer since it requires 3 scans/seeks and his is only 2. So only use this if you need to.

With addRN AS
(
  SELECT *,
        row_number() OVER (PARTITION BY id ORDER BY ded1) AS rn
  FROM tbl
)
SELECT ids.id, r1.ded1 as ded1, r2.ded1 as ded2, r3.ded1 ad ded3, r4.ded1 as ded4
FROM (SELECT DISTINCT id FROM tbl) ids
LEFT JOIN addRN r1 ON ids.id = r1.id AND rn = 1
LEFT JOIN addRN r2 ON ids.id = r2.id AND rn = 2
LEFT JOIN addRN r3 ON ids.id = r3.id AND rn = 3
LEFT JOIN addRN r3 ON ids.id = r3.id AND rn = 4