Home > Mobile >  How to create dynamic row in sql without inserting a value?
How to create dynamic row in sql without inserting a value?

Time:09-23

I have a requirement to add dynamic rows based on results fetched by SQL query. I've written a query that shows result something like the below:

Value Name
1 Test 1
2 Test 2
. .
n n

The above SQL result will return a dynamic number of rows. (Number of rows not fixed)

So I want to add a column with values like Parent1, Parent2, and so on based on the number of rows. Suppose my query returns a total of 300 rows then the first row should be named as Parent1 in column Value and In name both, Then result of my query until the 150th row then another dynamic row with value column as Parent2 field and so on like below table.

Value Name
Parent1 Parent 1
1 Test 1
2 Test 2
. .
Parent2 Parent2
151 Test 151
. .
n n

Please Note : I can not use DDL or DML Commands to achive this.

CodePudding user response:

Suppose this is your original query

select 
  to_char(rownum) value, 'Test '||rownum name 
from dual 
connect by level <= 6
;
    VALUE NAME                                         
---------- ----------
         1 Test 1                                       
         2 Test 2                                       
         3 Test 3                                       
         4 Test 4                                       
         5 Test 5
         6 Test 6 

and you want to introdues two header Parent lines.

You may use NTILEto split the original query in two parts ordering on some column (here VALUE)

 NTILE(2) OVER (ORDER BY VALUE) nt

Change the number in NTILE to increase the split.

The query below uses the original query as base, calculates the NTILE for the split, adds with UNION ALL the Parent rows.

Most importantly covers the correct order using the NTILE number (nt), the source (first parent row than data) and the value.

with dt as ( /* your original query */
select 
  to_char(rownum) value, 'Test '||rownum name 
from dual 
connect by level <= 6
)
select VALUE, NAME,
NTILE(2) OVER (ORDER BY VALUE) nt, /* modify to change split */
1 src
from dt
union all
select 
 'Parent'||rownum value,
 'Parent'||rownum name,
 rownum nt, 0 src
from dual connect by level <= 2 /* modify to change split */
order by nt, src, value; 

VALUE                                          NAME                                                   NT        SRC
---------------------------------------------- ---------------------------------------------- ---------- ----------
Parent1                                        Parent1                                                 1          0
1                                              Test 1                                                  1          1
2                                              Test 2                                                  1          1
3                                              Test 3                                                  1          1
Parent2                                        Parent2                                                 2          0
4                                              Test 4                                                  2          1
5                                              Test 5                                                  2          1
6                                              Test 6                                                  2          1

CodePudding user response:

The query below will generate a list of parents/non-parents using CONNECT BY. You can change the 300 to the number of rows you want to generate and you change the 150 in the query to have a parent generated that many number of rows.

    SELECT LEVEL,
           CASE
               WHEN MOD (LEVEL, 150) = 0 OR LEVEL = 1
               THEN
                   'Parent' || TO_CHAR (TRUNC (LEVEL / 150)   1)
               ELSE
                   TO_CHAR (LEVEL)
           END    AS VALUE,
           CASE
               WHEN MOD (LEVEL, 150) = 0 OR LEVEL = 1
               THEN
                   'Parent' || TO_CHAR (TRUNC (LEVEL / 150)   1)
               ELSE
                   'Test ' || TO_CHAR (LEVEL)
           END    AS VALUE
      FROM DUAL
CONNECT BY LEVEL <= 300;

CodePudding user response:

A similar approach, more dynamic.

col value for 9999
col name  for a20
define limit = &1
define split = &2
select level as lvl,
        case
               when mod (level, &&split) = 0 or level = 1
               then
                   'parent' || to_char (trunc (level / &&split)   1)
               else
                   to_char (level)
           end    as name,
         case
               when mod (level, &&split) = 0 or level = 1
               then
                   'parent' || to_char (trunc (level / &&split)   1)
               else
                   'test ' || to_char (level)
           end as value
from dual
connect by level <= &&limit
/

Executed as script, you inform two parameters, the total number of values and the split value.

SQL> @generate.sql 100 50
old   3:                when mod (level, &&split) = 0 or level = 1
new   3:                when mod (level, 50) = 0 or level = 1
old   5:                    'parent' || to_char (trunc (level / &&split)   1)
new   5:                    'parent' || to_char (trunc (level / 50)   1)
old  10:                when mod (level, &&split) = 0 or level = 1
new  10:                when mod (level, 50) = 0 or level = 1
old  12:                    'parent' || to_char (trunc (level / &&split)   1)
new  12:                    'parent' || to_char (trunc (level / 50)   1)
old  17: connect by level <= &&limit
new  17: connect by level <= 100

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
         1 parent1              parent1
         2 2                    test 2
         3 3                    test 3
         4 4                    test 4
         5 5                    test 5
         6 6                    test 6
         7 7                    test 7
         8 8                    test 8
         9 9                    test 9
        10 10                   test 10
        11 11                   test 11

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
        12 12                   test 12
        13 13                   test 13
        14 14                   test 14
        15 15                   test 15
        16 16                   test 16
        17 17                   test 17
        18 18                   test 18
        19 19                   test 19
        20 20                   test 20
        21 21                   test 21
        22 22                   test 22

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
        23 23                   test 23
        24 24                   test 24
        25 25                   test 25
        26 26                   test 26
        27 27                   test 27
        28 28                   test 28
        29 29                   test 29
        30 30                   test 30
        31 31                   test 31
        32 32                   test 32
        33 33                   test 33

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
        34 34                   test 34
        35 35                   test 35
        36 36                   test 36
        37 37                   test 37
        38 38                   test 38
        39 39                   test 39
        40 40                   test 40
        41 41                   test 41
        42 42                   test 42
        43 43                   test 43
        44 44                   test 44

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
        45 45                   test 45
        46 46                   test 46
        47 47                   test 47
        48 48                   test 48
        49 49                   test 49
        50 parent2              parent2
        51 51                   test 51
        52 52                   test 52
        53 53                   test 53
        54 54                   test 54
        55 55                   test 55

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
        56 56                   test 56
        57 57                   test 57
        58 58                   test 58
        59 59                   test 59
        60 60                   test 60
        61 61                   test 61
        62 62                   test 62
        63 63                   test 63
        64 64                   test 64
        65 65                   test 65
        66 66                   test 66

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
        67 67                   test 67
        68 68                   test 68
        69 69                   test 69
        70 70                   test 70
        71 71                   test 71
        72 72                   test 72
        73 73                   test 73
        74 74                   test 74
        75 75                   test 75
        76 76                   test 76
        77 77                   test 77

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
        78 78                   test 78
        79 79                   test 79
        80 80                   test 80
        81 81                   test 81
        82 82                   test 82
        83 83                   test 83
        84 84                   test 84
        85 85                   test 85
        86 86                   test 86
        87 87                   test 87
        88 88                   test 88

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
        89 89                   test 89
        90 90                   test 90
        91 91                   test 91
        92 92                   test 92
        93 93                   test 93
        94 94                   test 94
        95 95                   test 95
        96 96                   test 96
        97 97                   test 97
        98 98                   test 98
        99 99                   test 99

       LVL NAME                 VALUE
---------- -------------------- ----------------------------------------------
       100 parent3              parent3

100 rows selected.
  • Related