Transform vertical into horizontal list

Shell Programming and Scripting
1

Hi,

I am creating a script that will pull data from database. The only thing missing now is that i have to transform the lines into horizontal list.

EXAMPLE

2015-07-15 09:00:00.0    |TCSERVER01     |5354                
2015-07-15 09:01:00.0    |TCSERVER01     |6899                
2015-07-15 09:02:00.0    |TCSERVER01     |3857                
2015-07-15 09:03:00.0    |TCSERVER01     |3380                
2015-07-15 09:04:00.0    |TCSERVER01     |3835                
2015-07-15 09:05:00.0    |TCSERVER01     |5357                
2015-07-15 09:00:00.0    |TCSERVER02     |5580                
2015-07-15 09:01:00.0    |TCSERVER02     |8979                
2015-07-15 09:02:00.0    |TCSERVER02     |3722                
2015-07-15 09:03:00.0    |TCSERVER02     |3325                
2015-07-15 09:04:00.0    |TCSERVER02     |3743                
2015-07-15 09:05:00.0    |TCSERVER02     |5006                
2015-07-15 09:00:00.0    |TCSERVER03     |2923                
2015-07-15 09:01:00.0    |TCSERVER03     |4412                
2015-07-15 09:02:00.0    |TCSERVER03     |2215                
2015-07-15 09:03:00.0    |TCSERVER03     |2010                
2015-07-15 09:04:00.0    |TCSERVER03     |2013                
2015-07-15 09:05:00.0    |TCSERVER03     |3712                
2015-07-15 09:00:00.0    |TCSERVER04     |2441                
2015-07-15 09:01:00.0    |TCSERVER04     |3464                
2015-07-15 09:02:00.0    |TCSERVER04     |1910                
2015-07-15 09:03:00.0    |TCSERVER04     |1978                
2015-07-15 09:04:00.0    |TCSERVER04     |2093                
2015-07-15 09:05:00.0    |TCSERVER04     |2673 

OUTPUT

LOGTIME					SERVER01		SERVER02		SERVER03		SERVER04
2015-07-15 09:00:00.0	5354			5580			2923			2441
2015-07-15 09:01:00.0	6899			8979			4412			3464
2015-07-15 09:02:00.0	3857			3722			2215			1910
2015-07-15 09:03:00.0	3380			3325			2010			1978
2015-07-15 09:04:00.0	3835			3743			2013			2093
2015-07-15 09:05:00.0	5357			5006			3712			2673

Hope you can help me out.

2

Hello reignangel2003 ,

kindly use code tags for commands/inputs/codes which you are using in your posts as per forum rules.
Following script may help you in same.

awk -F"|" '{A[$1]=A[$1]?A[$1] OFS $3:$3} END{for(i in A){print i OFS A}}' input1 > output1
echo "LOGTIME SERVER01 SERVER02 SERVER03 SERVER04"
sort -k1 output1

output will be as follows.

LOGTIME SERVER01 SERVER02 SERVER03 SERVER04
2015-07-15 09:00:00.0  5354  5580  2923  2441
2015-07-15 09:01:00.0  6899  8979  4412  3464
2015-07-15 09:02:00.0  3857  3722  2215  1910
2015-07-15 09:03:00.0  3380  3325  2010  1978
2015-07-15 09:04:00.0  3835  3743  2013  2093
2015-07-15 09:05:00.0  5357  5006  3712  2673

Thanks,
R. Singh

3

actually it works. But then upon testing, there output did not align with that of the expected output. take this sample input

SAMPLE

2015-07-15 09:01:00.0 |TCSERVER01 |10965
2015-07-15 09:02:00.0 |TCSERVER01 |4752
2015-07-15 09:03:00.0 |TCSERVER01 |4805
2015-07-15 09:04:00.0 |TCSERVER01 |3690
2015-07-15 09:01:00.0 |TCSERVER02 |8703
2015-07-15 09:02:00.0 |TCSERVER02 |3757
2015-07-15 09:03:00.0 |TCSERVER02 |4458
2015-07-15 09:04:00.0 |TCSERVER02 |2897
2015-07-15 09:01:00.0 |TCSERVER03 |8491
2015-07-15 09:02:00.0 |TCSERVER03 |4199
2015-07-15 09:03:00.0 |TCSERVER03 |3600
2015-07-15 09:04:00.0 |TCSERVER03 |3773
2015-07-15 09:00:00.0 |TCSERVER04 |6549
2015-07-15 09:01:00.0 |TCSERVER04 |11268
2015-07-15 09:02:00.0 |TCSERVER04 |4572
2015-07-15 09:03:00.0 |TCSERVER04 |3881
2015-07-15 09:04:00.0 |TCSERVER04 |3510

as you can see, only tcserver04 has a logtime for "2015-07-15 09:00:00" so the output should only reflect on the TCSERVER04 which is shown below.

OUTPUT

LOGTIME                 TCSERVER01   TCSERVER02   TCSERVER03    TCSERVER04
2015-07-15 09:00:00.0	                                        6549
2015-07-15 09:01:00.0   10965        8703         8491          11268
2015-07-15 09:02:00.0   4752         3757         4199          4572
2015-07-15 09:03:00.0   4805         4458         3600          3881
2015-07-15 09:04:00.0   3690         2897         3773          3510

Hoping that you could help me on my query

4

Try this adaptation to Ravinder's suggestion

awk -F"|" '{A[$1]=A[$1]?A[$1] OFS $3:$3} END{for(i in A){print i, A}}' OFS='\t\t'

--
It will not work correctly if there are values missing, like:

2015-07-15 09:00:00.0	                                        6549

I do not know if that is just in your sample?

5

That's right. it will not work when there are values missing. will it be possible to put "0" instead so that the approach will work.

6

I had not read your last remark properly. --
Try something like:

awk '
  BEGIN {
    FS="|"
    OFS="\t"
  }
  {
    A[$1]
    C[$1,$2]=$3
  }
  !B[$2]++ {
    I[++n]=$2
  } 
  END { 
    s="LOGTIME" OFS OFS OFS
    for(j=1; j<=n; j++) {
      s=s OFS I[j]
    }
    print s
    for(i in A) {
      s=i
      for(j=1; j<=n; j++)
        s=s OFS OFS C[i,I[j]]+0
      print s | "sort"
    }
  }
'  file

If you leave out +0 it will print empty fields rather than zeroes, but it should still be aligned.

1 Like