Shell Programming and Scripting

Hi All,

I am working with a delimited file generated by a system. It contains customer response to some questions. Source system can add/remove questions, which will result into addition/removal of columns in the file.

Below is sample data with three questions (q1,q2,q3)

Code:

sid,rid,q1,q2,q3
1,1,a,b,c
1,2,x,y,z

I want to have data as below. Basically converting columns to rows and have only question, answer in the columns. This way i can create rows even if some question gets added/removed to the file.

Code:

sid,rid,question,answer
1,1,q1,a
1,1,q2,b
1,1,q3,c
1,2,q1,x
1,2,q2,y
1,2,q3,z

I looked at this site but could not find a solution. Thanks in advance for helping me.

Code:

awk '
BEGIN{FS=OFS=",";print "sid,rid,question,answer"}
NR==1{split($0,a,",");next}
{for (i=3;i<=NF;i++) print $1,$2,a[i],$i}' infile

Awesome!! This works well.
Appreciate if you can explain a little about the code and how it is working.
I could understand first line, Could not follow from $0,a, in 2nd line.