Please help me, the question is probably stupid, but I'm at a dead end. There is a table with the following data:

I would like to come to something like this, so that Num is displayed in one cell, the first and last values are displayed in the Start number and Date start fields, respectively, and their number is also counted in one cell:

CodePudding user response：

What you want is literally done in this query:

with t(Num, Start_number, Date_start) as (
select 111 , 225 , '11.11.22' from dual union all
select 111 , 223 , '9.11.22' from dual union all
select 111 , 220 , '9.11.22' from dual union all
select 222 , 347 , '11.11.22' from dual union all
select 222 , 345 , '11.11.22' from dual union all
select 222 , 343 , '10.11.22' from dual
)
select case when p.is_first = 1 then p.num end as num
     , p.start_number
     , p.date_start
     , case when p.is_first = 1 then p.cnt end as num
from (
  select t.*
       , case when start_number = max(start_number) over (partition by num) then 1 else 0 end as is_first
       , case when start_number = min(start_number) over (partition by num) then 1 else 0 end as is_last
       , count(*) over (partition by num) as cnt
  from t
) p
where p.is_first = 1 or p.is_last = 1

fiddle

However I agree with commenters this should be done at GUI level rather than SQL level. It does not make sense to alternate null and nonnull values (is it for some report?). You can utilize the p ("precomputation") subquery anyway, just change outer query then.

CodePudding user response：

You can try the following SQL code:

SELECT
  Num,
  StartNumber,
  DateStart,
  [Count]
FROM
  (
    SELECT
      Num,
      [Count],
      (SELECT MAX(StartNumber) FROM table AS t2 WHERE (t2.Num = t1.Num) AND (t2.DateStart = t1.MaxDateStart)) AS StartNumber,
      MaxDateStart AS DateStart
    FROM
      (
      SELECT
        Num,
        COUNT(*) AS [Count],
        MAX(DateStart) AS MaxDateStart
      FROM
        table
      GROUP BY
        Num
      ) AS t1
      
    UNION
      
    SELECT
      Num,
      [Count],
      (SELECT MIN(StartNumber) FROM table AS t2 WHERE (t2.Num = t1.Num) AND (t2.DateStart = t1.MinDateStart)) AS StartNumber,
      MinDateStart AS DateStart
    FROM
      (
      SELECT
        Num,
        COUNT(*) AS [Count],
        MIN(DateStart) AS MinDateStart
      FROM
        table
      GROUP BY
        Num
      ) AS t1
  ) AS t  
ORDER BY
  Num, DateStart DESC

This code works without OVER for different RDBMS.