How to pivot the rows values to existing columns so as to retain one row for one user?

Note: Each level will have only one value for each user.

CodePudding user response：

Given your premise of one single value on aggregated fields for each user, you can just use aggregation with the MAX funtion for each non-aggregated field:

SELECT id, [user], 
       MAX(manager_id)   AS manager_id, 
       MAX(manager_name) AS manager_name, 
       MAX(hierarchy)  1 AS hierarchy,
       MAX(level_1)      AS level_1, 
       MAX(level_2)      AS level_2, 
       MAX(level_3)      AS level_3, 
       MAX(level_4)      AS level_4 
FROM tab
GROUP BY id, [user]

Check the demo here.

CodePudding user response：

I would use STRING_AGG here because I dislike the risk to lose data if multiple levels per row are NOT NULL or a user has multiple names per level in different rows.

If this doesn't happen, the result is the same as when using MAX or MIN.

If it does happen, we will get every name per level instead of one only.

SELECT id, 
MAX(user_id) AS user_id, 
MAX(manager_id) AS manager_id, 
MAX(manager_name) AS manager_name, 
MAX(hierarchy)  1 AS hierarchy, 
STRING_AGG(level_1,',') AS level_1,
STRING_AGG(level_2,',') AS level_2,
STRING_AGG(level_3,',') AS level_3,
STRING_AGG(level_4,',') AS level_4  
FROM yourtable
GROUP BY id;

Replicate the difference here: db<>fiddle

Even considering your statement there is always one name per level only, I would not risk that. In a week or a month, things might differ.

Sidenote: I renamed "user" to "user_id" in my example because I refuse to use SQL key words as column name or table name. I recommend to avoid that if not urgently necessary.