-1

I have a requirement as- if the user selects checkboxes containing 1,2,3,4 of type 1 and 2,3 of type2 then I should display as (1-4)type1 , (2-3)type2 as the output. We have to do it in backend. I have used LISTAGG but couldn't achieve the desired output. The user selects the checkbox and we have the values stored in Oracle database. Any inputs will be greatly helpful.

For Ex, the following is my data.

Type Options
1 1
1 2
2 2
1 3
2 3
1 4

Using LISTAGG I could obtain : select Type , listagg (Option, ',') WITHIN GROUP (ORDER BY Type) selectedOption from ( select .... )

Type selectedOption
1 1,2,3,4
2 2,3

Desired Output:

Type selectedOption
1 1-4
2 2-3
MT0
  • 143,790
  • 11
  • 59
  • 117
Shravya M
  • 25
  • 1
  • 6
  • You may want to look at Oracle `CASE` statements, but it's not really clear to me what you are asking. Is the user selecting this using SQL in the database? Are they selecting a value that is "1", "2", "3" or "4" or are they selecting "1,2,3,4" . Is the "backend" the database? – a2800276 Mar 05 '21 at 17:31
  • Can you please post some sample data and needed result? As is, it's not clear what kind of data you are starting from and the logic you need to implement – Aleksej Mar 05 '21 at 17:31
  • Can you please add (as text, not as an image) the result you are expecting? And maybe use the preview and formatting functions provided in the editor. – a2800276 Mar 05 '21 at 18:02
  • This looks like GAPS AND ISLAND problem. Please share your sample data or you can directly google this term. – Ankit Bajpai Mar 05 '21 at 19:09
  • @AnkitBajpai Yeah i have searched for GAPS AND ISLAND. Looks like that can help me. Thank you. – Shravya M Mar 05 '21 at 19:36
  • You can try it by yourself. If you have any problem, please post some sample data here, We will help you out. – Ankit Bajpai Mar 05 '21 at 19:38

1 Answers1

2

You can use MATCH_RECOGNIZE to find the range boundaries and then aggregate:

SELECT type,
       LISTAGG(
         CASE
         WHEN range_start = range_end
         THEN TO_CHAR( range_start )
         ELSE range_start || '-' || range_end
         END,
         ','
       ) WITHIN GROUP ( ORDER BY mn ) AS grouped_values
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY type
  ORDER BY value
  MEASURES
    FIRST( value ) AS range_start,
    LAST( value )  AS range_end,
    MATCH_NUMBER() AS mn
  ONE ROW PER MATCH
  PATTERN ( successive_values* last_value )
  DEFINE successive_values AS NEXT( value ) <= LAST( value ) + 1
)
GROUP BY type

Which, for the sample data:

CREATE TABLE table_name ( type, value ) AS
SELECT 1, COLUMN_VALUE
FROM TABLE( SYS.ODCINUMBERLIST( 1, 2, 10, 17, 3, 15, 4, 16, 5, 7, 8 ) )
UNION ALL
SELECT 2, COLUMN_VALUE
FROM TABLE( SYS.ODCINUMBERLIST( 2, 3 ) )

Outputs:

TYPE | GROUPED_VALUES  
---: | :---------------
   1 | 1-5,7-8,10,15-17
   2 | 2-3

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117