awk help required to group output and print a part of group line and original line

Shell Programming and Scripting
1

Hi,
Need awk help to group and print lines to format the output as shown below

INPUT FORMAT
set echo on
set heading on
set spool on
/* SCHEMA1 / CREATE TABLE T1;
/ SCHEMA1 / CREATE TABLE T2;
/ SCHEMA1 / CREATE TABLE T3;
/ SCHEMA1 / CREATE TABLE T4;
/ SCHEMA1 / CREATE TABLE T5;
/ SCHEMA2 / CREATE TABLE T1;
/ SCHEMA2 / CREATE TABLE T2;
/ SCHEMA2 / CREATE TABLE T3;
/ SCHEMA3 / CREATE TABLE T1;
/ SCHEMA3 / CREATE TABLE T2;
/ SCHEMA3 / CREATE TABLE T3;
/ SCHEMA4 */ CREATE TABLE T1;
set feedback on
set spool off

DESIRED OUTPUT
set echo on
set heading on
set spool on
connect SCHEMA1
/* SCHEMA1 / CREATE TABLE T1;
/ SCHEMA1 / CREATE TABLE T2;
/ SCHEMA1 / CREATE TABLE T3;
/ SCHEMA1 / CREATE TABLE T4;
/ SCHEMA1 / CREATE TABLE T5;
connect SCHEMA2
/ SCHEMA2 / CREATE TABLE T1;
/ SCHEMA2 / CREATE TABLE T2;
/ SCHEMA2 / CREATE TABLE T3;
connect SCHEMA3
/ SCHEMA3 / CREATE TABLE T1;
/ SCHEMA3 / CREATE TABLE T2;
/ SCHEMA3 / CREATE TABLE T3;
connect SCHEMA4
/ SCHEMA4 / CREATE TABLE T1;
set feedback on
set spool off
I am looking forward to add the connect lines where all the SQL lines start with / SCHEMAn */ values as a consistent pattern

i was able to sort the distict values of the pattern using the below awk

nawk '/^\/\* /{dup[$2]++;if (dup[$2]>=1){name[$2]=$2}};END{for(i in name)print name[i]} ' inputfile

Output
-------
SCHEMA1
SCHEMA2
SCHEMA3
SCHEMA4

but i am not sure how to place these lines in before the patterns and skip till the next pattern is found.

Thanks in advance,
Rajan

2

This should work for you:

awk '/SCHEMA/ && a != $2{a=$2;print "connect "a}1' file
3

Worked perfect.. Thanks a lot....

4

nawk '{
if(index($2,"SCHEMA")!=0 && temp!=$2)
{
print "connect",$2
temp=$2
print $0
}
else
print
}' file

5

Hi Danmero! i got a doubt here what does '1' do in the below code, what all i can use in place of 1. I am using solaris 10, and the above is not working in my system.
"awk '/SCHEMA/ && a != $2{a=$2;print "connect "a}1' file"

6

On Solaris use /usr/xpg4/bin/awk