About number merger (urgent !!)

zxfred

The table is as follows
Id--number
pjhm--varchar
eg:
id pjhm
1 1
1 2
1 3
twenty one
twenty two
Find a sql to implement number combination based on id number,

1 1-3
2 1-2

ohguro

select ID, min (PJHM), max (PJHM)
  from (
  select ID, PJHM-rownum as c, rownum, PJHM from (
  SELECT * FROM (
    SELECT 1 AS ID, '001' AS PJHM FROM DUAL UNION ALL
    SELECT 1 AS ID, '002' AS PJHM FROM DUAL UNION ALL
    SELECT 1 AS ID, '003' AS PJHM FROM DUAL UNION ALL
    SELECT 1 AS ID, '005' AS PJHM FROM DUAL UNION ALL
    SELECT 1 AS ID, '006' AS PJHM FROM DUAL UNION ALL
    SELECT 2 AS ID, '001' AS PJHM FROM DUAL UNION ALL
    SELECT 2 AS ID, '005' AS PJHM FROM DUAL UNION ALL
    SELECT 2 AS ID, '006' AS PJHM FROM DUAL
    order by ID, PJHM)
  group by ID, c
  ORDER BY 1, 2;

panda_ren

create or replace function f_get_str (in_id number) return varchar2
is
v_str varchar (50);
v_result varchar (500);
cursor c_str is
    select pjhm from table1 where id = in_id;
begin
    open c_str;
    v_result: = '';
    loop
        fetch c_str into v_str;
        
        exit when c_str% NOTFOUND;
        v_result: = v_result || trim (v_str) || '-';
    end loop;
    close c_str;
    return v_result;
end f_get_str;
/
select id, f_get_str (id)
from table1
order by id;

zxfred

Write a static statement ...

ohguro

select ID, min (PJHM) || '-' || max (PJHM)
  from (
  select ID, PJHM-rownum as c, rownum, PJHM from (
  SELECT * FROM (
    SELECT 1 AS ID, '001' AS PJHM FROM DUAL UNION ALL
    SELECT 1 AS ID, '002' AS PJHM FROM DUAL UNION ALL
    SELECT 1 AS ID, '003' AS PJHM FROM DUAL UNION ALL
    SELECT 1 AS ID, '005' AS PJHM FROM DUAL UNION ALL
    SELECT 1 AS ID, '006' AS PJHM FROM DUAL UNION ALL
    SELECT 2 AS ID, '001' AS PJHM FROM DUAL UNION ALL
    SELECT 2 AS ID, '005' AS PJHM FROM DUAL UNION ALL
    SELECT 2 AS ID, '006' AS PJHM FROM DUAL
    order by ID, PJHM)
  group by ID, c
  ORDER BY 1, 2;

zxfred

Didn't make it clear,
I don't want static statements

ohguro

Still don't understand what statement the landlord wants?
select ID, min (PJHM) || '-' || max (PJHM)
from (select ID, PJHM-rownum as c, rownum, PJHM from (
SELECT * FROM table_name order by ID, PJHM)
group by ID, c
ORDER BY 1, 2;

whbilly

select ID, min (PJHM) || '-' || max (PJHM)
from (select ID, PJHM-rownum as c, rownum, PJHM from (
SELECT * FROM table_name order by ID, PJHM)
group by ID, c
ORDER BY 1, 2;