Oracle SQL - Create identifier for couple of values-CodePudding

I'm struggling with the following problem, I have the follwing data in a table:

Param ID	Param Val	Other Cols
1	15	XXX
1	15	XXX
1	16	XXX
1	16	XXX
2	21	XXX
2	21	XXX
2	22	XXX
2	22	XXX

I would like to select a new colum in order to create 4 sets of data to have all the possible combination between the values of parameter 1 and 2; so I would like to obtain something like this:

Set	Param ID	Param Val	Other Cols
1	1	15	XXX
2	1	15	XXX
3	1	16	XXX
4	1	16	XXX
1	2	21	XXX
3	2	21	XXX
2	2	22	XXX
4	2	22	XXX

So for example for the Set 1 I will have the Couple of values 15 and 21, for the set 2 the values 15 and 22 etc etc.

I tried using different analytic functions, but I was not able to have what I need. Thanks in advance.

CodePudding user response：

Try to use analytic function like here:

WITH
    tbl AS
        (
            Select 1 "ID", 15 "VAL" From Dual Union All
            Select 1 "ID", 15 "VAL" From Dual Union All
            Select 1 "ID", 16 "VAL" From Dual Union All
            Select 1 "ID", 16 "VAL" From Dual Union All
            Select 2 "ID", 21 "VAL" From Dual Union All
            Select 2 "ID", 21 "VAL" From Dual Union All
            Select 2 "ID", 22 "VAL" From Dual Union All
            Select 2 "ID", 22 "VAL" From Dual 
        )
            
        
Select
    Sum(1) OVER(Partition By ID Order By ID, VAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "SET_ID",
    ID "ID",
    VAL "VAL"
From
    tbl

The result should be:

SET_ID	ID	VAL
1	1	15
2	1	15
3	1	16
4	1	16
1	2	21
2	2	21
3	2	22
4	2	22

It is summing 1 from first to current row (for every row) of each ID (Partition By) and ordered by ID, VAL. More about basics of analytic functions here ( https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174 )
You can select the sets (from this resulting set as my_set) like here:

Select 
    SET_ID "SET_ID",
    LISTAGG(To_Char(ID) || '/' || To_Char(VAL), ', ') WITHIN GROUP (Order By ID, VAL) "IDS"
From
    my_set 
Group By
    SET_ID
Order By
    SET_ID

SET_ID	IDS
1	1/15, 2/21
2	1/15, 2/21
3	1/16, 2/22
4	1/16, 2/22

Regards...

ADDITION AFTER THE COMMENT
If you want the combinations then you will have to use the MODEL clause. To do that you should prepare the data (cte named grid) a bit so you could do the addressing to the particular data and manage all the combinations you want. It looks like here:

    grid AS
        ( Select  Distinct
                  Sum(1) OVER(Partition By ID Order By ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "IDS_TOTAL_ORDER",
                  Sum(1) OVER(Partition By VAL Order By VAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "VALS_TOTAL_ORDER",
                  ID "ID",  
                  VAL "VAL"
            From
                  tbl   
            Order By ID 
        )
Select     SET_ID, ID, VAL
From (   Select   0 "SET_ID", IDS_TOTAL_ORDER, VALS_TOTAL_ORDER, ID "ID", VAL "VAL"
         From     grid  
     )
    MODEL
        Dimension By (ID, IDS_TOTAL_ORDER, VALS_TOTAL_ORDER)
        Measures(SET_ID, VAL)
        RULES
            (
                SET_ID[1, ANY, ANY] = CV(IDS_TOTAL_ORDER),
                SET_ID[2, 1, 1] = CV(IDS_TOTAL_ORDER),
                SET_ID[2, 2, 2] = CV(IDS_TOTAL_ORDER)   1,
                SET_ID[2, 3, 1] = CV(IDS_TOTAL_ORDER) - 1,
                SET_ID[2, 4, 2] = CV(IDS_TOTAL_ORDER)
            )
Order By  ID, IDS_TOTAL_ORDER

This way you can get any combination. Here is your result:

SET_ID	ID	VAL
1	1	15
2	1	15
3	1	16
4	1	16
1	2	21
3	2	21
2	2	22
4	2	22

More about MODEL clause: https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm

Sets now looks like here:

SET_ID	IDS_VALS
1	1/15, 2/21
2	1/15, 2/22
3	1/16, 2/21
4	1/16, 2/22

Regards...

CodePudding user response：

Despite a good hint on the MODEL clause, I guess I will go for a solution combining cross join and unpivot, maybe it is not the best, but it fit my needs.

WITH tbl AS
(
       Select 1 "ID", 15 "VAL" From Dual Union All
       Select 1 "ID", 16 "VAL" From Dual Union All
       Select 2 "ID", 21 "VAL" From Dual Union All
       Select 2 "ID", 22 "VAL" From Dual )
SELECT *
FROM   (
              SELECT ROWNUM AS SET_ID,
                     id1,
                     id4
              FROM   (
                            SELECT
                                   CASE a.id
                                          WHEN 1 THEN a.val
                                          ELSE 0
                                   END AS id1,
                                   CASE a.id
                                          WHEN 2 THEN a.val
                                          ELSE 0
                                   END AS id2,
                                   CASE b.id
                                          WHEN 1 THEN b.val
                                          ELSE 0
                                   END AS id3,
                                   CASE b.id
                                          WHEN 2 THEN b.val
                                          ELSE 0
                                   END AS id4
                            FROM   tbl a,
                                   tbl b)
              WHERE  id2 = 0
              AND    id3 = 0) UNPIVOT (VAL FOR ID IN (id1 AS '1',
                                                      id4 AS '2'))

that results in :

SET_ID	ID	VAL
1	1	15
1	2	21
2	1	15
2	2	22
3	1	16
3	2	21
4	1	16
4	2	22

tried with all the combination of source data, and it seems work :)