I have a couple of records and wanted to change those in to column.

Ex:
------

Key Value
1 Sno:111
Sname:xxxx
Dept:xxxx
2 Sno:222
Sname:xxxx
Dept:xxxx

need to change this output as,

key sno sname Dept
1 111 xxxx xxxx
2 222 xxxx xxxx

Is there any easy way to do this? Please let me know.

Sure, actually that is easier than what you are doing. My guess, if you did a view source, the source would Either use SUBHEADS or HEADING, and fully specify the values, etc. What you want is
PRINT SNO SNAME DEPT BY KEY, which you can easily get in the GUI, By bringing up the file in the REPORT PAINTER, and clicking on each of the fields SNO, SNAME and DEPT, and then choosing BY, and click on KEY.

SNO,Sname and Dept are not field, they are just
text. I wanted them to make those as a field.

sno, sname and Dept are the values for the field VALUE. I have only two field in my table. KEY and VALUE are the two fields.

This is one possibility that may work for you or else give you the inspiration that we all seek

Master -

FILE=keyvalue, SUFFIX=FOC
SEGNAME=ROOT_SEG, SEGTYPE=S1, $
FIELD=KEY, ALIAS=KEY, FORMAT=I5, $
FIELD=VALUE, ALIAS=VALUE, FORMAT=A20, $

Code -

CREATE FILE KEYVALUE
-RUN

MODIFY FILE KEYVALUE
DATA
1, Sno:111,$
1, Sname:xxxx,$
1, Dept:xxxx,$
2, Sno:222,$
2, Sname:xxxx,$
2, Dept:xxxx,$
END

DEFINE FILE KEYVALUE
SNO/A20 = IF VALUE CONTAINS 'Sno' THEN SUBSTR(20,VALUE,5,20,15,SNO);
SNAME/A20 = IF VALUE CONTAINS 'Sname' THEN SUBSTR(20,VALUE,7,20,13,SNAME);
DEPT/A20 = IF VALUE CONTAINS 'Dept' THEN SUBSTR(20,VALUE,6,20,14,DEPT);
END

TABLE FILE KEYVALUE
SUM SNO SNAME DEPT
BY KEY
END

I am be missing something but why not just
do..
TABLE FILE ..
PRINT
KEY AS 'KEY' AND VALUE AS 'sno sname Dept'
...

with some spacing in the Title if it had to fit over the VALUE field.

Tony - Value field will have dynamic value. I mean, we will have sno,sname,dept or sno,sname or sno,sname,deptid,dept,...

Gerald - we can't do the way you said because I need this report in Excel format.

Right now I thought of doing it using define but it didn't work.

Define file test
field1/a10=if value eq 'sno' then value else '';
field2/a10=if value eq 'sname' then value else '';
end

table file test
print field1 field2
end

but this didn't work.

Since your VALUE field may contain "Sno:111", how can you do IF VALUE EQ 'sno' THEN ...

Shouldn't you be doing

IF VALUE LIKE 'sno%' THEN ...

or

IF VALUE CONTAINS 'sno' THEN ...

and watch out, IF is case sensitive.

ohhh sorry I mentioned wrongly as EQ.
I'm doing contains .

So .......

Basically what I suggested then?

It looks like you want to use Tony's suggestion, with the addition of using POSIT to find the start of the string that contains the value ie:
SNO/A20 = IF VALUE CONTAINS 'Sno' THEN SUBSTR(20,VALUE,POSIT(VALUE,20,'Sno',3,'I2')+4, 20,15,SNO);
You might also need to something similar to find the substring end point, and its length (instead of the 20,15) otherwise the SNO would contain the sname,dept etc if it was in the string, too.

Kamesh,



If you want a truly versatile method where you do not know what the initial value of the VALUE field is then you could get messy and use this code (against my previous datafile)
-

DEFINE FILE KEYVALUE
<br />-* Find the position of the colon marking the end of the data label
<br />  COLEND/I5 = POSIT(VALUE,20,':',1,COLEND);
<br />-* And use it to split out the label itself
<br />  NEWCOL/A20 = SUBSTR(20,VALUE,1,COLEND-1,COLEND-1,NEWCOL);
<br />-* And the actual field value
<br />  NEWVAL/A20 = SUBSTR(20,VALUE,COLEND+1,20,20-COLEND,NEWVAL);
<br />END
<br />
<br />-* Running a BY on the label field we get the unique occurences
<br />TABLE FILE KEYVALUE
<br />BY NEWCOL
<br />-* Which we save and then read back in as variables
<br />ON TABLE SAVE AS NEWCOLS
<br />END
<br />-RUN
<br />
<br />-* Set a count for identifying the new variables
<br />-SET &Cnt = 0 ;
<br />-* And read them in using &LINES to control the number
<br />-REPEAT :ColRead &LINES TIMES ;
<br />-SET &Cnt = &Cnt + 1 ;
<br />-READ NEWCOLS, &Col&Cnt.EVAL.A20.
<br />-:ColRead
<br />
<br />-* Transfer the number of variables we have to a control variable
<br />-SET &NumDefines = &Cnt ;
<br />-* Reset the count
<br />-SET &Cnt = 0 ;
<br />-* And ADD to the defines we already have<br />DEFINE FILE KEYVALUE ADD
<br />-* And add a define for each unique data label
<br />-REPEAT :ColDefine &NumDefines TIMES ;
<br />-SET &Cnt = &Cnt + 1 ;
<br /> &Col&Cnt.EVAL/A20 = IF NEWCOL CONTAINS '&Col&Cnt.EVAL' THEN NEWVAL ELSE IF KEY EQ LAST KEY THEN LAST &Col&Cnt.EVAL ELSE '' ;
<br />-:ColDefine
<br />END
<br />
<br />-* And finally add each new column into the TABLE request
<br />TABLE FILE KEYVALUE
<br />SUM
<br />-SET &Cnt = 0 ;
<br />-REPEAT :ColWrite &NumDefines TIMES ;
<br />-SET &Cnt = &Cnt + 1 ;
<br /> &Col&Cnt.EVAL<br />-:ColWrite
<br />BY KEY
<br />END

Which gives the output of -

KEY Dept Sname Sno 
<br />1   xxxx xxxx  111 
<br />2   xxxx xxxx  222 

The set length of variable at 20 chars is a bit 'naf' to say the least, but it gives you the general idea and, more importantly, gives you the result that you're after.

This message has been edited. Last edited by: <Mabel>,