How can I take value from cell and display for all rows that belongs to current id?

Example of table:

Output:

CodePudding user response：

You can achieve this by using a subquery or a common table expression (CTE) to get the value for each ID, and then joining the original table with the CTE to update the "parameter" column for all rows belonging to that ID.

Here is an example using a CTE in SQL:

WITH cte AS (
SELECT id, MIN(parameter) AS parameter
FROM mytable
WHERE event = 'session_start'
GROUP BY id
)
SELECT t.id, t.event, c.parameter
FROM mytable t
JOIN cte c
ON t.id = c.id;

CodePudding user response：

I wouldn't use a CTE or subquery here (both are far too complicated for this use case) and I also don't think using an aggregate function like MIN is correct here. This would also replace other values if present, not only NULL values.

In my opinion, this is a perfect use case for FIRST_VALUE with COALESCE.

You didn't tag your DBMS, but most today's DBMS will correctly execute following query:

SELECT id, event, 
COALESCE(parameter, FIRST_VALUE(parameter) 
  OVER (PARTITION BY id ORDER BY id)) AS parameter
FROM mytable;

This will get the first value for each id. And this value will be set only in case the original value is NULL, otherwise the value will not be changed.

Try out here