[Solved] Convert Row To column

Hi Folks,
I am using db2 command -> db2 list tablespace show detail

Tablespace ID                        = 10
 Name                                 = TSCDDHLMSUM
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 320
 Useable pages                        = 288
 Used pages                           = 224
 Free pages                           = 64
 High water mark (pages)              = 224
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 11
 Name                                 = SYSTOOLSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1024
 Useable pages                        = 1020
 Used pages                           = 92
 Free pages                           = 928
 High water mark (pages)              = 92
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 4
 Number of containers                 = 1

As you can see, there are 2 tablespaces information shown in row format, I would like to have show it into column format

  Tablespace ID   Name             Type                          Contents ....
  -------------    -----             ----                           -------
  10                  TSCDDHLMSUM Database managed space  ....
  11                  SYSTOOLSPACE Database managed space ....

Thanks in advance for all the input.

Add all the fields to print

$ awk -v RS="" -v FS="[\n=]" ' NR == 1 { print $1, $3, $5; print "----- ----- -----"  } { print $2, $4, $6 } ' file

Hello,

Could you please try following and let me know if this helps.

rows=`awk -F"=" '{print $1}' convert_row_col_coinvert12111 | xargs -n 1`
columns=`awk -F"=" '{print $2}' convert_row_col_coinvert12111 | xargs -n 1`
echo $rows
echo "----------------------------------------------------------------"
echo $columns

Thanks,
R. Singh

If your input header is fix format then you can placed it in BEGIN block.

 awk -F"=" 'BEGIN{print "Tablespace ID | Name | Type|  Contents | State|  Total pages | Useable pages|  Used pages|  Free pages|  High water mark (pages)| Page size (bytes) |Extent size (pages) |Prefetch size (pages)| Number of containers"} /=/{if(/Tablespace/ && NR>1) { printf "\n";} printf $2"|";} END{printf "\n"}' filename

Try :

awk '
function out(){
                print s
                i = s = ""
              }
        ++i<=4{
                gsub(/^[[:space:]]+/,x,$NF)
                s = s ? s OFS $NF: $NF			 
		if(!h){
			gsub(/[[:space:]]+$/,x,$1)
			hdr = hdr ? hdr OFS $1 : $1
		      }
	      }
           !NF{
		if(hdr){ 
			print hdr; h = 1 ; hdr = ""
		       }
                out()
                next
              }
           END{
                out()
              }
     ' FS='=' OFS='\t' file

$ bash tester
Tablespace ID	 Name	 Type	 Contents
10	TSCDDHLMSUM	Database managed space	All permanent data. Regular table space.
11	SYSTOOLSPACE	Database managed space	All permanent data. Large table space.

---------- Post updated at 07:52 PM ---------- Previous update was at 07:46 PM ----------

--edit--

remove this ++i<=4 in above code to get all fields as column and change OFS='\t' for output separator.

Hi All,
I use anbu23 approach and modified a little bit :

db2 list tablespaces show detail |awk 'NR > 2 {print $0}'|awk -v RS="" -v FS="[\n]" '{ print $2 ,$8,$9 }'|awk ' $2 >0 && $3 > 0   { printf "%18s %10d %10d \n
", $3,$7,$11}'|awk 'NR == 1 {print "TBSP NAME           TOTAL PAGES    SIZE(G)| USED PAGES    SIZE(G)| FREE PAGES    SIZE(G)|"; print "-----------       ----
---------  ---------| ----------    -------| ----------  ---------|"} {printf "%-20s %10d %10d| %10d %10d| %10d %10d|\n", $1,$2,$2*32768/1024/1024/1024,$3,$3
*32768/1024/1024/1024,$2-$3,($2-$3)/1024/1024/1024}'|grep -v "="

Thank You Everyone who spend time figure it out.