Geek asks and answers

How to replace NULL in a result set with the last NOT NULL value in the same column?

My colleague has a problem with sql query: -

As an example, take two temporary tables as an example: -

select 'John' as name,10 as value into #names
UNION ALL SELECT 'Abid',20 
UNION ALL SELECT 'Alyn',30 
UNION ALL SELECT 'Dave',15;

select 'John' as name,'SQL Expert' as job into #jobs
UNION ALL SELECT 'Alyn','Driver' 
UNION ALL SELECT 'Abid','Case Statement';

We run the following query in the tables to give us a combined set of results: -

select #names.name, #names.value, #jobs.job
FROM #names left outer join #jobs
on #names.name = #jobs.name

name    value    job
John    10       SQL Expert
Abid    20       Case Statement
Alyn    30       Driver
Dave    15       NULL

Since "Dave" does not exist in the #jobs table, it is set to NULL as expected.

My colleague wants to modify the query, so each NULL value is assigned the same value as the previous record.

So the above would be: -

name    value    job
John    10       SQL Expert
Abid    20       Case Statement
Alyn    30       Driver
Dave    15       Driver

Note that Dave is now a "driver"

A sequence can have more than one NULL value,

name    value    job
John    10       SQL Expert
Abid    20       Case Statement
Alyn    30       Driver
Dave    15       NULL
Joe     15       NULL
Pete    15       NULL

In this case, Dave, Joe and Pete should be “Driver”, since “Driver” is the last non-empty record.

+5
2

, , . Common Table Exions (CTE) OUTER APPLY, persion. id , , . , .

, , . , .

, SQL Fiddle.

, SQL Fiddle .

Script:

 CREATE TABLE names
    (
            id      INT         NOT NULL IDENTITY
      ,     name    VARCHAR(20) NOT NULL
      ,     value   INT         NOT NULL
    );

    CREATE TABLE jobs
    (
            id  INT         NOT NULL
      ,     job VARCHAR(20) NOT NULL
    );

    INSERT INTO names (name, value) VALUES
      ('John', 10),
      ('Abid', 20),
      ('Alyn', 30),
      ('Dave', 40),
      ('Jill', 50),
      ('Jane', 60),
      ('Steve', 70);

    INSERT INTO jobs (id, job) VALUES
      (1, 'SQL Expert'),
      (2, 'Driver' ),
      (5, 'Engineer'),
      (6, 'Barrista');

    ;WITH empjobs AS
    (
        SELECT
        TOP 100 PERCENT n.id
                    ,   n.name
                    ,   n.value
                    ,   job
        FROM            names n 
        LEFT OUTER JOIN jobs j
        on              j.id = n.id
        ORDER BY        n.id
    ) 
    SELECT      e1.id
            ,   e1.name
            ,   e1.value
            ,   COALESCE(e1.job , e2.job) job FROM empjobs e1
    OUTER APPLY (
                  SELECT 
                  TOP 1     job 
                  FROM      empjobs     e2
                  WHERE     e2.id   < e1.id
                  AND       e2.job  IS NOT NULL
                  ORDER BY  e2.id   DESC
                ) e2;

:

ID  NAME    VALUE  JOB
--- ------  -----  -------------
1   John      10   SQL Expert
2   Abid      20   Driver
3   Alyn      30   Driver
4   Dave      40   Driver
5   Jill      50   Engineer
6   Jane      60   Barrista
7   Steve     70   Barrista
+6

"" ? , "" . , "" , , .

CREATE TABLE #names
    (
            id      INT         NOT NULL IDENTITY
      ,     name    VARCHAR(20) NOT NULL
      ,     value   INT         NOT NULL PRIMARY KEY
    );

    CREATE TABLE #jobs
    (
            name  VARCHAR(20)         NOT NULL
      ,     job VARCHAR(20) NOT NULL
    );

    INSERT INTO #names (name, value) VALUES
      ('John', 10),
      ('Abid', 20),
      ('Alyn', 30),
      ('Dave', 40),
      ('Jill', 50),
      ('Jane', 60),
      ('Steve', 70);

    INSERT INTO #jobs (name, job) VALUES
      ('John', 'SQL Expert'),
      ('Abid', 'Driver' ),
      ('Alyn', 'Engineer'),
      ('Dave', 'Barrista');

with Partial as (
  select
  #names.name,
  #names.value,
  #jobs.job as job
  FROM #names left outer join #jobs
  on #names.name = #jobs.name
)
  select
    name,
    value,
    (
      select top 1 job
      from Partial as P
      where job is not null
      and P.value <= Partial.value
      order by value desc
    )
  from Partial;

, , .

0

More articles:


All Articles