insert data in datatable with sql query

Question

I have query like this (select id, reffid, someData from myTable):

id     reffid    someData
8        10       text1
9        10       text2
10       11       text3
11       11       text4
12       11       text5
13       11       text6
14       12       text7
15       12       text8
16       12       text9
17       13       text10
18       14       text11

I need query which will do the following: if I have less than 4 reffid with same values, than add rows with same same reffid and in someData add -. Result query should look like this:

id     reffid    someData
8        10       text1
9        10       text2
???      10          -
???      10          -
10       11       text3
11       11       text4
12       11       text5
13       11       text6
14       12       text7
15       12       text8
16       12       text9
???      12          -
17       13       text10
???      13          - 
???      13          - 
???      13          - 
18       14       text11
???      14          - 
???      14          - 
???      14          -

How to accomplish that?

Which DBMS are you using? Postgres? Oracle?

user330315
– user330315

2013-09-05 07:37:25 +00:00
Commented Sep 5, 2013 at 7:37 — user330315
– user330315, Commented Sep 5, 2013 at 7:37

peterm · Accepted Answer · 2013-09-05 07:50:44Z

Try

SELECT t.id, l.reffid, COALESCE(t.somedata, '-') someData
  FROM
(
  SELECT reffid, rnum
    FROM
  (
    SELECT DISTINCT reffid
      FROM table1
  ) q CROsS JOIN 
  (
    SELECT 1 rnum UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
  ) n
) l LEFT JOIN 
(
  SELECT id, reffid, somedata, 
         ROW_NUMBER() OVER (PARTITION BY reffid ORDER BY id) rnum
    FROM table1
) t
   ON l.reffid = t.reffid
  AND l.rnum   = t.rnum

Output:

|     ID | REFFID | SOMEDATA |
|--------|--------|----------|
|      8 |     10 |    text1 |
|      9 |     10 |    text2 |
| (null) |     10 |        - |
| (null) |     10 |        - |
|     10 |     11 |    text3 |
|     11 |     11 |    text4 |
|     12 |     11 |    text5 |
|     13 |     11 |    text6 |
|     14 |     12 |    text7 |
|     15 |     12 |    text8 |
|     16 |     12 |    text9 |
| (null) |     12 |        - |
|     17 |     13 |   text10 |
| (null) |     13 |        - |
| (null) |     13 |        - |
| (null) |     13 |        - |
|     18 |     14 |   text11 |
| (null) |     14 |        - |
| (null) |     14 |        - |
| (null) |     14 |        - |

Here is SQLFiddle demo

Collectives™ on Stack Overflow

insert data in datatable with sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related