×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Transpose column to line but with a difference

 Forum Search FAQs Links MVPs

## Transpose column to line but with a difference

(OP)

I have another conundrum...I hope someone can help me with?

My input data - I have only shown 5 column's (Packs), there are in fact 56 columns and 350 records

#### CODE -->

CH#,M Pack,M+ Pack,L Pack,XL Pack,etc...
100,1,1,1,1
101,1,1,1,1
102,1,1,1,1
103,1,1,1,1
104,1,1,1,1
105,1,1,1,1
106,1,1,1,1
107,1,1,1,1
108,0,0,0,0
109,0,1,1,1
110,0,0,0,0
111,0,1,1,1
112,0,0,1,1
113,0,0,0,0
114,1,1,1,1
115,1,1,1,1
116,0,0,0,1
etc..... 

This is the output I am seeking

#### CODE -->

M Pack,100,101,102,103,104,105,106,107,114,115,
M+ Pack,100,101,102,103,104,105,106,107,109,111,114,115,
L Pack,100,101,102,103,104,105,106,107,109,111,112,114,115,
XL Pack,100,101,102,103,104,105,106,107,109,111,112,114,115,116, 

As always, thanks in advance.
Replies continue below

### RE: Transpose column to line but with a difference

What have you tried so far and where in your code are you stuck ?

### RE: Transpose column to line but with a difference

(OP)
I have tried many awk "transpose" suggestions from the net and also tried to amend them to give me the output I want but to no avail.
I manually generated the output shown above with the first piece of code shown below and changed the $5 to suit column and cut and paste into code section. When it comes to manipulating arrays, I always struggle. Incidently the version of gawk I am using 4.0.1 #### CODE --> #!/bin/bash FILE="test" gawk -F"," '{ if($5 == 1) { printf $1"," } }'$FILE

exit 0

awk 'BEGIN {FS="," ; OFS=","}
{
for(c = 1; c <= NF; c++) {
a[c, NR] = $c } if(max_nf < NF) { max_nf = NF } } END { for(r = 1; r <= NR; r++) { for(c = 1; c <= max_nf; c++) { printf("%s,",a[r, c]) } print "" } } '$FILE

exit 0

awk -F',' 'BEGIN {i=0} {for (i=1;i<=NF;i++) print $i}'$FILE

exit 0

gawk 'BEGIN {FS=","}

{for(i=1;i<=NF;i++) ; a[NR,i]=$i} END{ for(i=1;i<=NF;i++){ for(j=1;j<=NR;j++){ line=line sep a[j,i] sep=FS } print line line=sep="" } }'$FILE

exit 0

gawk 'BEGIN {FS=","}

{for(i=1;i<=NF;i++)
a[NR,i]=$i } END { for(i=1;i<=NF;i++){ for(j=1;j<=NR;j++){ line=line sep a[j,i] sep=FS } print line line=sep="" } }'$FILE

exit 0

gawk -F"," 'BEGIN {
max_x =0;
max_y =0;
}

{
max_y++;
for( i=1; i<=NF; i++ )
{
if (i>max_x) max_x=i;
A[i,max_y] = $i; } } END { for ( x=1; x<=max_x; x++ ) { for ( y=1; y<=max_y; y++ ) { if ( (x,y) in A ) printf "%s",A[x,y]; if ( y!=max_y ) printf " "; } printf "\n"; } }'$FILE

exit 0

gawk -F"," 'BEGIN {OFS=","}   {
#pkgl=$8 pkgl[$8]++
} END {
for (i in pkgl)
print pkgl[i],i

}' $FILE exit 0  gawk version 4.0.4 ### RE: Transpose column to line but with a difference What about this ? #### CODE gawk -F, 'NR==1{for(i=2;i<=NF;++i)a[i]=$i}
{for(i=2;i<=NF;++i)if($i==1)a[i]=a[i]","$1}
END{for(i=2;i in a;++i)print a[i]}
' $FILE  ### RE: Transpose column to line but with a difference (OP) Yep...That works!....needs a bit of tidy up on the last output. I would never have worked it out, Thank-you. gawk version 4.0.1 ### RE: Transpose column to line but with a difference needs a bit of tidy up on the last output Can you, please, share your final code ? ### RE: Transpose column to line but with a difference (OP) Certainly, Your code works perfectly. All that was missing was an "exit 0". I cut and paste your code above my futile attempts shown above. so after your code, it also ran... #### CODE --> FILE="test" gawk -F"," '{ if($5 == 1) { printf $1"," } }'$FILE
exit 0 

In my haste to reply and thank-you, I did not double check it at the time.
Once again thank-you.

gawk version 4.0.1

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

#### Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!