repeat a single row in all rows oracle-CodePudding

How can I repeat a value from one row to the others? Here are the table and the desired result:

Normal table:

|SURVEY_ID  |    SONDA| DATA_CRIA   | ANSWER

CHK-SON-CS-07  PR-05    06/04/2022   1
CHK-SON-CS-07           06/04/2022   2
CHK-SON-CS-07           06/04/2022   3
CHK-SON-CS-07           06/04/2022   4

I need to repeat the PR-05 in all the rows.

Desired result

SURVEY_ID   |   SONDA | DATA_CRIA  | ANSWER
CHK-SON-CS-07   PR-05   06/04/2022  1
CHK-SON-CS-07   PR-05   06/04/2022  2
CHK-SON-CS-07   PR-05   06/04/2022  3
CHK-SON-CS-07   PR-05   06/04/2022  4

CodePudding user response：

Adding to @Huda Khan answer, given SONDA is not null on any row number (not just first row).

select 
(select sonda from test 
where sonda is not null fetch first 1 rows only) sonda 
from test 
connect by level=(select count(*) from test);

or - with CTE

with cte as
(select sonda from test 
where sonda is not null 
fetch first 1 rows only)
select cte.sonda from test,cte

CodePudding user response：

Simplest is something like this:

select survey_id, min(sonda) over () as sonda, data_cria, answer
from   <table_name>
;

The analytic function min returns the only non-null value from the column; as an analytic function (as opposed to its aggregate version), the value will be copied to each row of the input table.

More likely, you will want something like this where there are groups of rows (by survey_id, or perhaps by survey_id and data_cria), with one non-null sonda in each group, and you will want to do the same "copying" within each group (not over the entire table). If so, you can easily adapt the solution: instead of over () you will use a partition clause, like over(partition by survey_id), or over (partition by survey_id, date_cria).

CodePudding user response：

SAMPLE TABLE:

select * from test_table


SURVEY_ID    |  SONDA  | DATA_CRIA  | ANSWEAR
CHK-SON-CS-07   PR-05   06.04.2022    1
CHK-SON-CS-07           06.04.2022    2
CHK-SON-CS-07           06.04.2022    3
CHK-SON-CS-07           06.04.2022    4
CHK-SON-CS-07           06.04.2022    5

WITH THIS QUERY:

  select t1.survey_id, t2.sonda, t1.data_cria, t1.answer
  from test_table t1, 
  (select survey_id,sonda from test_table where sonda is not null) t2
  where t1.survey_id=t2.survey_id

RESULT:

SURVEY_ID    |  SONDA  | DATA_CRIA  | ANSWEAR
CHK-SON-CS-07   PR-05   06.04.2022    1
CHK-SON-CS-07   PR-05   06.04.2022    2
CHK-SON-CS-07   PR-05   06.04.2022    3
CHK-SON-CS-07   PR-05   06.04.2022    4
CHK-SON-CS-07   PR-05   06.04.2022    5

CodePudding user response：

You can use lag(ignore nulls):

SELECT COALESCE(SONDA,lag(SONDA ignore nulls) over (order by ANSWER))
FROM TABLENAME;

OR Go with the simplest solution:

SELECT
t1.SURVEY_ID ,
CASE WHEN t1.SONDA IS NULL
THEN (SELECT top 1 t2.SONDA
FROM TABLE AS t2
WHERE t2.ANSWER< t1.ANSWER AND
t2.SONDA IS NOT NULL
)
ELSE t1.SONDA
END AS SONDA,
t1.ANSWER
FROM TABLE AS t1;