Row to column conversion

[Roopesh Francis_1]

Hi Guru's,

I have a requirement to convert  text paragraph as below.Could you please someone help on this,

My Input is

====
6XN1LK2B xxxxx07 /dev/rhdisk0 292968750
6XN1MBA1 xxxxx07 /dev/rhdisk1 292968750
6XN1MXKA xxxxx07 /dev/rhdisk2 292968750
6XN1MWEH xxxxx07 /dev/rhdisk3 292968750
2350BED0 xxxxx07 /dev/rhdisk5 62914560
404FB47D xxxxx07 /dev/rhdisk6 62914560
434FB47D xxxxx07 /dev/rhdisk7 62914560
454FFA70 xxxxx07 /dev/rhdisk8 62914560
464FFA70 xxxxx07 /dev/rhdisk9 62914560
8050AF2D xxxxx07 /dev/rhdisk10 31457280
8150AF2D xxxxx07 /dev/rhdisk11 31457280
8250AF2D xxxxx07 /dev/rhdisk12 31457280
8350D043 xxxxx07 /dev/rhdisk13 62914560
8450D043 xxxxx07 /dev/rhdisk14 62914560
====
6XN1LK2B xxxxx06 /dev/rhdisk0 292968750
6XN1MBA1 xxxxx06 /dev/rhdisk1 292968750
6XN1MXKA xxxxx06 /dev/rhdisk2 292968750
6XN1MWEH xxxxx06 /dev/rhdisk3 292968750
2350BED0 xxxxx06 /dev/rhdisk5 62914560
404FB47D xxxxx06 /dev/rhdisk6 62914560
434FB47D xxxxx06 /dev/rhdisk7 62914560
454FFA70 xxxxx06 /dev/rhdisk8 62914560
464FFA70 xxxxx06 /dev/rhdisk9 62914560
8050AF2D xxxxx06 /dev/rhdisk10 31457280
8150AF2D xxxxx06 /dev/rhdisk11 31457280
8250AF2D xxxxx06 /dev/rhdisk12 31457280
8350D043 xxxxx06 /dev/rhdisk13 62914560
8450D043 xxxxx06 /dev/rhdisk14 62914560
====
6XN1LK2B xxxxx05 /dev/rhdisk0 292968750
6XN1MBA1 xxxxx05 /dev/rhdisk1 292968750
6XN1MXKA xxxxx05 /dev/rhdisk2 292968750
6XN1MWEH xxxxx05 /dev/rhdisk3 292968750
2350BED0 xxxxx05 /dev/rhdisk5 62914560
404FB47D xxxxx05 /dev/rhdisk6 62914560
434FB47D xxxxx05 /dev/rhdisk7 62914560
454FFA70 xxxxx05 /dev/rhdisk8 62914560
464FFA70 xxxxx05 /dev/rhdisk9 62914560
8050AF2D xxxxx05 /dev/rhdisk10 31457280
8150AF2D xxxxx05 /dev/rhdisk11 31457280
8250AF2D xxxxx05 /dev/rhdisk12 31457280
8350D043 xxxxx05 /dev/rhdisk13 62914560
8450D043 xxxxx05 /dev/rhdisk14 62914560
====

Output should be link this.

6XN1LK2B xxxxx07 /dev/rhdisk0 292968750  6XN1LK2B xxxxx06 /dev/rhdisk0 292968750  6XN1LK2B xxxxx05 /dev/rhdisk0 292968750
6XN1MBA1 xxxxx07 /dev/rhdisk1 292968750  6XN1MBA1 xxxxx06 /dev/rhdisk1 292968750  6XN1MBA1 xxxxx05 /dev/rhdisk1 292968750
6XN1MXKA xxxxx07 /dev/rhdisk2 292968750  6XN1MXKA xxxxx06 /dev/rhdisk2 292968750  6XN1MXKA xxxxx05 /dev/rhdisk2 292968750
6XN1MWEH xxxxx07 /dev/rhdisk3 292968750  6XN1MWEH xxxxx06 /dev/rhdisk3 292968750  6XN1MWEH xxxxx05 /dev/rhdisk3 292968750
2350BED0 xxxxx07 /dev/rhdisk5 62914560  2350BED0 xxxxx06 /dev/rhdisk5 62914560  2350BED0 xxxxx05 /dev/rhdisk5 62914560
404FB47D xxxxx07 /dev/rhdisk6 62914560  404FB47D xxxxx06 /dev/rhdisk6 62914560  404FB47D xxxxx05 /dev/rhdisk6 62914560
434FB47D xxxxx07 /dev/rhdisk7 62914560  434FB47D xxxxx06 /dev/rhdisk7 62914560  434FB47D xxxxx05 /dev/rhdisk7 62914560
454FFA70 xxxxx07 /dev/rhdisk8 62914560  454FFA70 xxxxx06 /dev/rhdisk8 62914560  454FFA70 xxxxx05 /dev/rhdisk8 62914560
464FFA70 xxxxx07 /dev/rhdisk9 62914560  464FFA70 xxxxx06 /dev/rhdisk9 62914560  464FFA70 xxxxx05 /dev/rhdisk9 62914560
8050AF2D xxxxx07 /dev/rhdisk10 31457280  8050AF2D xxxxx06 /dev/rhdisk10 31457280  8050AF2D xxxxx05 /dev/rhdisk10 31457280
8150AF2D xxxxx07 /dev/rhdisk11 31457280  8150AF2D xxxxx06 /dev/rhdisk11 31457280  8150AF2D xxxxx05 /dev/rhdisk11 31457280
8250AF2D xxxxx07 /dev/rhdisk12 31457280  8250AF2D xxxxx06 /dev/rhdisk12 31457280  8250AF2D xxxxx05 /dev/rhdisk12 31457280
8350D043 xxxxx07 /dev/rhdisk13 62914560  8350D043 xxxxx06 /dev/rhdisk13 62914560  8350D043 xxxxx05 /dev/rhdisk13 62914560
8450D043 xxxxx07 /dev/rhdisk14 62914560  8450D043 xxxxx06 /dev/rhdisk14 62914560  8450D043 xxxxx05 /dev/rhdisk14 62914560

[Dennis Handly]

You have one file where you have multiple groups (separated by "===="?) and you want to combine them and put each group as a column group where the key is the first field?

Do you care if you sort the whole file and columns can come from any part of the file?

I.e instead of:

6XN1LK2B xxxxx07 /dev/rhdisk0 292968750  6XN1LK2B xxxxx06 /dev/rhdisk0 292968750  6XN1LK2B xxxxx05 /dev/rhdisk0 292968750

You get:

6XN1LK2B xxxxx05 /dev/rhdisk0 292968750 /dev/rhdisk0 292968750  6XN1LK2B xxxxx06 /dev/rhdisk0 292968750

Do you care that the first column is sorted?  (Your example output isn't.)

I.e. Sort the whole file, if multiple rows with same key, create another column group.

[Roopesh Francis_1]

Hi Dennis,

Thanks for quick reply.

You have one file where you have multiple groups (separated by "===="?) and you want to combine them and put each group as a column group where the key is the first field?

yes.Correct.

Do you care if you sort the whole file and columns can come from any part of the file?

I.e instead of:

6XN1LK2B xxxxx07 /dev/rhdisk0 292968750  6XN1LK2B xxxxx06 /dev/rhdisk0 292968750  6XN1LK2B xxxxx05 /dev/rhdisk0 292968750

You get:

6XN1LK2B xxxxx05 /dev/rhdisk0 292968750 /dev/rhdisk0 292968750  6XN1LK2B xxxxx06 /dev/rhdisk0 292968750

I want text as it is.

basically first column is identical(6XN1LK2B,6XN1MBA1-luns) for all servers(XXXXX05,06,07) .My requirement is to check each lun avialability in all the servers. 

Do you care that the first column is sorted?  (Your example output isn't.)

I.e. Sort the whole file, if multiple rows with same key, create another column group.

Many  thanks once agian.

[Dennis Handly · Accepted solution]

>I want text as it is.

This is too hard, I'm just sorting and merging.  I did see duplicates for 8350D043 with multiple disks, so I merge if the first and third columns match:

# Sorts the data by column 1 and 3 then merges matches to same line

sed -e '/====/d' input-file | sort -k1,1 -k3,3 | awk '
BEGIN { key1 = ""; key2 = ""; save = "" }
key1 == $1 && key2 == $3 {
   save = save " " $0  # combine
   next
}
# no match
{
if (save != "")
   print save
key1 = $1
key2 = $3
save = $0
}
END {
if (save != "")
   print save
} '

Output looks like:

2350BED0 xxxxx05 /dev/rhdisk5 62914560 2350BED0 xxxxx06 /dev/rhdisk5 62914560 2350BED0 xxxxx07 /dev/rhdisk5 62914560
...
8350D043 xxxxx05 /dev/rhdisk13 62914560 8350D043 xxxxx06 /dev/rhdisk13 62914560 8350D043 xxxxx07 /dev/rhdisk13 62914560
8450D043 xxxxx05 /dev/rhdisk14 62914560 8450D043 xxxxx06 /dev/rhdisk14 62914560 8450D043 xxxxx07 /dev/rhdisk14 62914560

[Roopesh Francis_1]

Hi Dennis,

Thanks much.

I tried above code.

Its doesnt change anything in the input file.

# cat check
sed -e '/====/d'  | sort -k1,1 -k3,3 | awk '
BEGIN { key1 = ""; key2 = ""; save = "" }
key1 == $1 && key2 == $3 {
   save = save " " $0  # combine
   next
}
# no match
{
if (save != "")
   print save
key1 = $1
key2 = $3
save = $0
}
END {
if (save != "")
   print save
} ' input_file
# sh  check
6XN1LK2B gepvio08 /dev/rhdisk0 292968750
6XN1MBA1 gepvio08 /dev/rhdisk1 292968750
6XN1MXKA gepvio08 /dev/rhdisk2 292968750
6XN1MWEH gepvio08 /dev/rhdisk3 292968750
2350BED0 gepvio08 /dev/rhdisk5 62914560
404FB47D gepvio08 /dev/rhdisk6 62914560
434FB47D gepvio08 /dev/rhdisk7 62914560
454FFA70 gepvio08 /dev/rhdisk8 62914560
464FFA70 gepvio08 /dev/rhdisk9 62914560
8050AF2D gepvio08 /dev/rhdisk10 31457280
8150AF2D gepvio08 /dev/rhdisk11 31457280
8250AF2D gepvio08 /dev/rhdisk12 31457280
8350D043 gepvio08 /dev/rhdisk13 62914560
8450D043 gepvio08 /dev/rhdisk14 62914560
===
6XN1LK2B gepvio07 /dev/rhdisk0 292968750
6XN1MBA1 gepvio07 /dev/rhdisk1 292968750
6XN1MXKA gepvio07 /dev/rhdisk2 292968750
6XN1MWEH gepvio07 /dev/rhdisk3 292968750
2350BED0 gepvio07 /dev/rhdisk5 62914560
404FB47D gepvio07 /dev/rhdisk6 62914560
434FB47D gepvio07 /dev/rhdisk7 62914560
454FFA70 gepvio07 /dev/rhdisk8 62914560
464FFA70 gepvio07 /dev/rhdisk9 62914560
8050AF2D gepvio07 /dev/rhdisk10 31457280
8150AF2D gepvio07 /dev/rhdisk11 31457280
8250AF2D gepvio07 /dev/rhdisk12 31457280
8350D043 gepvio07 /dev/rhdisk13 62914560
8450D043 gepvio07 /dev/rhdisk14 62914560
===
6XN1LK2B gepvio06 /dev/rhdisk0 292968750
6XN1MBA1 gepvio06 /dev/rhdisk1 292968750
6XN1MXKA gepvio06 /dev/rhdisk2 292968750
6XN1MWEH gepvio06 /dev/rhdisk3 292968750
2350BED0 gepvio06 /dev/rhdisk5 62914560
404FB47D gepvio06 /dev/rhdisk6 62914560
434FB47D gepvio06 /dev/rhdisk7 62914560
454FFA70 gepvio06 /dev/rhdisk8 62914560
464FFA70 gepvio06 /dev/rhdisk9 62914560
8050AF2D gepvio06 /dev/rhdisk10 31457280
8150AF2D gepvio06 /dev/rhdisk11 31457280
8250AF2D gepvio06 /dev/rhdisk12 31457280
8350D043 gepvio06 /dev/rhdisk13 62914560
8450D043 gepvio06 /dev/rhdisk14 62914560
===

[Dennis Handly]

>It doesn't change anything in the input file.

Oops, I put the input-file on the wrong stage of the pipeline.  See corrected script.

(I initially tested with a here-doc.)

[Roopesh Francis_1]

Hi Dennis,

Its works perfectly fine now.

Many thanks.