Question: I have the below table with data

```
ID          Value                             Available
29000       Data1                               1
29000       Data2                               0
29001       Data1                               1
29002       Insert                              0
29002       Data1                               1
29003       Data2                               0

```

So basically I have a grouping of ID's and based on whether there is Data1 present in that grouping I have to insert another row in table with the value of Insert and whatever the Available value for Data1 is

Eventual result I want 

```
ID          Value                             Available
29000       Data1                               1
29000       Data2                               0
29000       Insert                              1
29001       Data1                               1
29001       Insert                              1
29002       Data1                               0
29002       Insert                              0 --29002 will be skipped since it already has Insert along with Data1
29003       Data2                               0  
29003       Data3                               0  --29003 will be skipped since there was no Data1 in that grouping

```

Can someone please tell me how can I achieve this.

Thanks

Answer: 
Hmmm . . . the extra rows are:

```
select id, 'Insert' as value, max(available) as available
from t
group by id
having sum(case when value = 'Insert' then 1 else 0 end) = 0;  -- no insert already

```

You can bring the data together with `union all`:

```
select id, value, available
from t
union all
select id, 'Insert' as value, max(available) as available
from t
group by id
having sum(case when value = 'Insert' then 1 else 0 end) = 0  -- no insert already
order by id;

```
You can try using subquery and union all:

```
select *
from table1
union all
select t1.ID, 'Insert', t1.Available
from table1 t1
where t1.Value = 'Data1'
and not exists
(
select 1
from table1 t2
where t2.ID = t1.ID
and t2.Value = 'Insert'
)
order by ID, Value, Available

```