Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Transform vertical into horizontal list

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Transform vertical into horizontal list
# 1  
Old 07-20-2015
Transform vertical into horizontal list
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.

Code:
EXAMPLE

Code:
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

Code:
OUTPUT

Code:
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.
Last edited by Don Cragun; 07-20-2015 at 07:36 PM.. Reason: Add CODE tags.
# 2  
Old 07-20-2015
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.
Code:
awk -F"|" '{A[$1]=A[$1]?A[$1] OFS $3:$3} END{for(i in A){print i OFS A[i]}}' input1 > output1
echo "LOGTIME SERVER01 SERVER02 SERVER03 SERVER04"
sort -k1 output1

output will be as follows.
Code:
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
Last edited by Scrutinizer; 07-28-2015 at 04:46 AM..
# 3  
Old 07-28-2015
actually it works. But then upon testing, there output did not align with that of the expected output. take this sample input

SAMPLE
Code:
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
Code:
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  
Old 07-28-2015
Try this adaptation to Ravinder's suggestion
Code:
awk -F"|" '{A[$1]=A[$1]?A[$1] OFS $3:$3} END{for(i in A){print i, A[i]}}' OFS='\t\t'


--
It will not work correctly if there are values missing, like:
Code:
2015-07-15 09:00:00.0	                                        6549

I do not know if that is just in your sample?
# 5  
Old 07-28-2015
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  
Old 07-28-2015
I had not read your last remark properly. --
Try something like:
Code:
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.
Last edited by Scrutinizer; 07-28-2015 at 05:09 AM..
This User Gave Thanks to Scrutinizer For This Post:
reignangel2003
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need to move vertical to horizontal using paste

Source file Name:hostname1 Masking views : Yes Storage Group Names : hostname1 device (5): Name:hostname2 Masking views : Yes Storage Group Names : hostname2 device (5): Name:hostname3 Masking views : no Storage Group Names : hostname3 device (5):... (9 Replies)
Discussion started by: ranjancom2000
9 Replies

2. UNIX for Dummies Questions & Answers

Print vertical to horizontal

Hi Masters, I need help to change my vertical data to horisontal input 2015-04-13|JS|741667 2015-04-13|JSJ|2272 2015-04-13|TMS|107099 2015-04-12|JMD|47945 2015-04-13|TM|760024 2015-04-13|JM|484508 2015-04-14|JMJ|318 2015-04-14|JSD|54436 2015-04-13|JM|15410 Output... (2 Replies)
Discussion started by: radius
2 Replies

3. UNIX for Dummies Questions & Answers

Change Vertical to Horizontal

I need to change data from vertical to horizontal but with condition input USA|80 AUS|40 BRA|33 VEGAS|40 KENTUCKY|50 NEWYORK|21 DARWIN|33 ADELAIDE|21 SAOPAOLO|44 RIO|89 GAPIZA|44 BENFLEX|32 AXIS|44 ACRE|56 HEIGHT|22 (5 Replies)
Discussion started by: radius
5 Replies

4. Shell Programming and Scripting

How do i do the vertical to horizontal??

51009 8746 8912 17986 20315 24998 5368 38934 7805 8566 (4 Replies)
Discussion started by: nikhil jain
4 Replies

5. Shell Programming and Scripting

awk in horizontal and vertical math

Based on input ail,UTT,id1_0,COMBO,21,24,21,19,85 al,UTHAST,id1_0,COMBO,342,390,361,361,1454 and awk code as awk -F, '{ K=0; for(i=NF; i>=(NF-4); i--) { K=K+$i; J=J+$i;} { print K } } END { for ( l in J ) printf("%s ",J); }' I'm trying to add columns and lines in single line. line... (6 Replies)
Discussion started by: busyboy
6 Replies

6. Shell Programming and Scripting

Vertical And Horizontal Pivoting

Hi All, My Input data is: A=1 B=2 My desired Output should be: A|B 1|2 Thanks in advance... (3 Replies)
Discussion started by: kmsekhar
3 Replies

7. UNIX for Dummies Questions & Answers

How to move vertical line to Horizontal...

How to move a vertical line to Horizontal line.....Can i use a tr command? code is: StudentID Java .Net C# I want to move this line like this: StudentID Java .Net C# Please use code tags! (3 Replies)
Discussion started by: Arsh10
3 Replies

8. UNIX for Dummies Questions & Answers

vertical to horizontal

dear all, i'm new to unix and i try to figure out the best case for making list of vertical text to become horizontal and skip the line 1 and 2. example text : Data DATE XXXXX MAX 47 53 49 51 48 48 7 46 51 8 25 (6 Replies)
Discussion started by: andrisetia
6 Replies

9. UNIX for Dummies Questions & Answers

Horizontal to vertical

Hi, Silly question, if I have an excel file that looks something like this: ................. Subject 1 Subject 2 Subject 3 Subject 4 Fever..............13...........9.............23..........14 Headache.........2............12...........18..........23... (3 Replies)
Discussion started by: Xterra
3 Replies

10. Shell Programming and Scripting

Vertical an horizontal pivoing in unix

Please help me to do Vertical an horizontal pivoing in unix in single run. The input file is like this- MRKT|PROD|PRD|FACT1|FACT2|FACT3|FACT4 M1|P1|PR1|F11|F12|F13|F14 M1|P1|PR2|F21|F22|F23|F24 M1|P1|PR3|F31|F32|F33|F34 M2|P2|PR1|F41|F42|F43|F44 M2|P2|PR2|F51|F53|F54|F55... (4 Replies)
Discussion started by: marut_ashu
4 Replies
Login or Register to Ask a Question