Need help putting output on one line

Good afternoon,

I have been searching the web, and these forums for help. I will try my best to explain the issue, and what my desired results are.

I am doing queries in MYSQL, and need the output to be sent to a file. That file needs to have things with the same ID on the same line. To give you an idea, if I query the names of drinks, it may come out like this:

type,name
juice,orange
juice,apple
pop,coca cola
pop,pepsi

How would I get the output to be like this:

juice,orange,apple
pop,coca cola,pepsi

Here is some sample output from the script I am working on:

sample output:

9993    10.10.50.0/24    blockAdminName    Username One
9993    10.10.50.0/24    block_name    Servers
9993    10.10.50.0/24    blockAdminEmail    username.one@people.com
9993    10.10.50.0/24    blockAdminPhone    312-555-1212
9993    10.10.50.0/24    blockAdminId    1234
9993    10.10.50.0/24    blockSecName    Username Two
9993    10.10.50.0/24    blockSecEmail    username.two@people.com
9993    10.10.50.0/24    blockSecPhone    312-555-2121
9993    10.10.50.0/24    blockSecId    7891
9993    10.10.50.0/24    blockTechName    Username Three
9993    10.10.50.0/24    blockTechEmail    username.three@people.com
9993    10.10.50.0/24    blockTechPhone    312-555-1313
9993    10.10.50.0/24    blockTechId    4567
9992    10.10.40.0/24    block_name    unknown
9992    10.10.40.0/24    blockAdminName    Username One
9992    10.10.40.0/24    blockAdminEmail    username.one@people.com
9992    10.10.40.0/24    blockAdminPhone    312-555-1212
9992    10.10.40.0/24    blockAdminId    1234
9992    10.10.40.0/24    blockSecName    Username Two
9992    10.10.40.0/24    blockSecEmail    username.two@people.com
9992    10.10.40.0/24    blockSecPhone    312-555-2121
9992    10.10.40.0/24    blockSecId    7891
9992    10.10.40.0/24    blockTechName    Username Three
9992    10.10.40.0/24    blockTechEmail    username.three@people.com
9992    10.10.40.0/24    blockTechPhone    312-555-1313
9992    10.10.40.0/24    blockTechId    4567
9991    10.10.30.0/24    block_name    unknown
9991    10.10.30.0/24    blockAdminName    Username One
9991    10.10.30.0/24    blockAdminEmail    username.one@people.com
9991    10.10.30.0/24    blockAdminPhone    312-555-1212
9991    10.10.30.0/24    blockAdminId    1234
9991    10.10.30.0/24    blockSecName    Username Two
9991    10.10.30.0/24    blockSecEmail    username.two@people.com

How can I get this to put everthing on three lines? Notice that there are only three different ID's (9991, 9992, 9993). Ideally, I would want something like this:

9992,10.10.50.0/24,blockAdminName="Username One",block_name="Servers",blockAdminEmail="username.one@people.com",blockAdminPhone="312-555-1212",blockAdminId="1234",blockSecName="Username Two",blockSecEmail="username.two@people.com",blockSecPhone="312-555-2121",blockSecId="7891",blockTechName="Username Three",blockTechEmail="username.three@people.com",blockTechPhone="312-555-1313",blockTechId="4567"

Notice that everything is on one line now. The first two columns are the same (9993 and 10.10.50.0/24). So it kept those and then put everything after that comma delimmited.

How can I accomplish this in Perl or Bash?

Thank you very much!!!!!

Hi, see if this works:

awk 'p!=$1{if(p)print RS; print p=$1,$2}{print OFS $3 "=\"" $4 "\""} END{print RS}' OFS=, ORS= infile

Might need something like this for the *Name fields:

awk 'p!=$1{print (p?RS:"")(p=$1),$2;} {for(i=4;i<=NF;i++)printf(i>4?" ":OFS $3 "=\"") $i; print "\"" } END{if(p)print RS}' OFS=, ORS= infile

When I run this, it doesn't get rid of the extra lines and condense them onto one line.

It does get rid of the first two fields on all lines except for line 1. How can I take this and put them all on the first line seperated by commas?

Here is what it looks like now:

9992 10.10.50.0/24
  blockAdminName="Username One"
  block_name="Servers"
  blockAdminEmail="username.one@people.com"
  blockAdminPhone="312-555-1212"
  blockAdminId="1234"
  blockSecName="Username Two"
  blockSecEmail="username.two@people.com"
  blockSecPhone="312-555-2121"
  blockSecId="7891"
  blockTechName="Username Three"
  blockTechEmail="username.three@people.com"
  blockTechPhone="312-555-1313"
  blockTechId="4567"

So thank you for getting me this far. How can I modify this awk statement to get them all into one line?

The awk is supposed to do that. What is your OS and version?

9993,10.10.50.0/24,blockAdminName="Username One",block_name="Servers",blockAdminEmail="username.one@people.com",blockAdminPhone="312-555-1212",blockAdminId="1234",blockSecName="Username Two",blockSecEmail="username.two@people.com",blockSecPhone="312-555-2121",blockSecId="7891",blockTechName="Username Three",blockTechEmail="username.three@people.com",blockTechPhone="312-555-1313",blockTechId="4567"
9992,10.10.40.0/24,block_name="unknown",blockAdminName="Username One",blockAdminEmail="username.one@people.com",blockAdminPhone="312-555-1212",blockAdminId="1234",blockSecName="Username Two",blockSecEmail="username.two@people.com",blockSecPhone="312-555-2121",blockSecId="7891",blockTechName="Username Three",blockTechEmail="username.three@people.com",blockTechPhone="312-555-1313",blockTechId="4567"
9991,10.10.30.0/24,block_name="unknown",blockAdminName="Username One",blockAdminEmail="username.one@people.com",blockAdminPhone="312-555-1212",blockAdminId="1234",blockSecName="Username Two",blockSecEmail="username.two@people.com"

When I do it on a Red Hat system, I get that.

Red Hat Enterprise Linux ES release 4

When I try it on Solaris 10 on an M4000, I get these errors:

-bash-3.00$ awk 'p!=$1{print (p?RS:"")(p=$1),$2;} {for(i=4;i<=NF;i++)printf(i>4?" ":OFS $3 "=\"") $i; print "\"" } END{if(p)print RS}'  /tmp/uda_out_2012.3.27.164244.sql 
awk: syntax error near line 1
awk: illegal statement near line 1
awk: syntax error near line 1
awk: illegal statement near line 1

On Solaris use /usr/xpg4/bin/awk rather than awk

A coworker helped me getting stuff on one line, with this:

cat $UDAFILE | while read BID NET DT DV
do
   if [ $BLOCKID -ne $BID ]
   then
      if [ ! -z "$NETWORK" ]
      then
         # Report UDA data
         /bin/echo -n "-c uda,set "
         /bin/echo -n "-Tipv4_subnet "
         /bin/echo -n "-a $NETWORK "
         /bin/echo -n "-o Agency "
         /bin/echo -n "-u qipman "
         /bin/echo -n "-p qipman "
         /bin/echo -n "--uda Subnet/Block_Tech_Name=\"$BlkTechName\" "
         /bin/echo -n "--uda Subnet/Block_Admin_Name=\"$BlkAdminName\" "
         /bin/echo -n "--uda Subnet/Block_Security_Name=\"$BlkSecName\" "
         /bin/echo -n "--uda Subnet/Block_Tech_UUPIC=\"$BlkTechId\" "
         /bin/echo -n "--uda Subnet/Block_Admin_UUPIC=\"$BlkAdminId\" "
         /bin/echo -n "--uda Subnet/Block_Security_UUPIC=\"$BlkSecId\" "
         /bin/echo
      fi
      # Reset UDF data values
      BlkAdmId=""
      BlkAdmName=""
      BlkSecId=""
      BlkSecName=""
      BlkTechId=""
      BlkTechName=""
      BLOCKID=$BID
   fi
   # read and store UDF data values
   NETWORK="`echo $NET | awk -F\/ '{print $1}'`"
   if [ "$DT" = "blockAdminId" ]
   then
      BlkAdminId="$DV"
   fi
   if [ "$DT" = "blockAdminName" ]
   then
      BlkAdminName="$DV"
   fi
   if [ "$DT" = "blockSecId" ]
   then
      BlkSecId="$DV"
   fi
   if [ "$DT" = "blockSecName" ]
   then
      BlkSecName="$DV"
   fi
   if [ "$DT" = "blockTechId" ]
   then
      BlkTechId="$DV"
   fi
   if [ "$DT" = "blockTechName" ]
   then
      BlkTechName="$DV"
   fi
done

But the problem is that it prints to standard out. How can I redirect all of that to a file? I can't just run the script and then send it a file, because at the beginning of the script it needs user input to get username and password:

echo -n "Enter your MYSQL username and press [ENTER]: "
read username
#
echo -n "Enter your password and press [ENTER]: "
stty -echo
password="`head -n 1`"
stty echo
echo

Any tips on how to modify this to send all output to file?

---------- Post updated at 11:55 AM ---------- Previous update was at 11:50 AM ----------

I'm getting the same from Solaris as I did on Linux:

9992 10.10.50.0/24   
 blockAdminName="Username One"
 block_name="Servers"   
 blockAdminEmail="username.one@people.com"
 bockAdminPhone="312-555-1212"
 blockAdminId="1234"
 blockSecName="Username Two"
 blockSecEmail="username.two@people.com"
 blockSecPhone="312-555-2121"
 blockSecId="7891"
 blockTechName="Username Three"
 blockTechEmail="username.three@people.com"
 blockTechPhone="312-555-1313"   blockTechId="4567"

To send all output into a file:

./script > filename

Or if you absolutely must do that in the script itself, have this as the first line:

exec 1>filename

By the by, that's a useless use of cat. You almost never need cat to read a file, programs are perfectly capable of reading files themselves, and even if they don't take filenames you can always just redirect into them...

You can rewrite the loop more efficiently as

while read LINE
do
...
done <filename

Thank you so much Corona!

I modified my script to have this:

while
do
...
done < $UDAFILE >> $UDAFILE.test.csv

And it is doing just what I want.

Perhaps I missed something, but how did we get from post#6 and #7 to #8 ?

You left out

OFS=, ORS=

at the end of your awk..

/usr/xpg4/bin/awk 'p!=$1{if(p)print RS; print p=$1,$2}{for(i=5;i<=NF;i++)$4=$4 FS $i; print OFS $3 "=\"" $4 "\""} END{print RS}' OFS=, ORS= infile

I'm sorry I did leave that off. It worked great!!!

I want to look at that awk statement piece by piece and see why it does what it does. Do you have time to explain what your awk statement does?

Here is the pseudo code for what it does:

for each input line
    if p <> field#1 then
        if p is assigned then print newline
        p = field#1
        print p field#2
    endif
    print ',"' field#4 ' ' field#5 ' ' field#6 ... ' ' field#N '"'
end
if p is assigned then print newline